A query with SELECT DISTINCT can be rewritten using GROUP BY. So this
[sql]
SELECT DISTINCT city
FROM HR.Employees
WHERE country = N’USA’ AND region = N’WA’ ;
[/sql]
is equivalent to:
[sql]
SELECT city
FROM HR.Employees
WHERE country = N’USA’ AND region = N’WA’
GROUP BY city ;
[/sql]
and you can’t use “ORDER BY birthdate” here either. The reason is the same for both queries. There may be many (more than one) rows with same city but different birthdate. Which one should be used for the ordering (if it was allowed?)
So, the alternative way is
[sql]
SELECT city
FROM HR.Employees
WHERE country = N’USA’ AND region = N’WA’
GROUP BY city
ORDER BY MIN(birthdate) ; — or MAX(birthdate)
[/sql]
Leave a Reply