If you work with SQL on Oracle you sometimes want to extract additional informations from a date field and also limit this result to a distinct set.
So for example to get all distinct years of a date field from a data set, you can use the following statement, which uses a subquery:

SELECT year FROM
(SELECT distinct TO_CHAR(wtp.delivery_date, 'YYYY') AS year FROM world_transport wtp)
ORDER BY year ASC;

We can further optimize the query, removing the subquery:

SELECT distinct TO_CHAR(wtp.delivery_date, 'YYYY') year
FROM world_transport wtp
ORDER BY year ASC;

If you now only want to order the result set, but also group it, remember that the alias year is not known when “GROUP BY” is invoked. To work around this simply use the same “TO_CHAR” function, instead of the alias: 

SELECT TO_CHAR(wtp.delivery_date, 'YYYY') year
FROM world_transport wtp
GROUP  BY TO_CHAR(wtp.delivery_date, 'YYYY')
ORDER BY year ASC;

If you want to know the year, but want to preserve the data type date, you can do this by using the function”TRUNC” instead of “TO_CHAR” function:

SELECT TRUNC(wtp.delivery_date, 'YEAR') year
FROM world_transport wtp
GROUP  BY TRUNC(wtp.delivery_date, 'YEAR')
ORDER BY year ASC;

Any comments or additions are welcome!