Using ORDER BY on SELECT DISTINCT in TSQL

Question

I am trying to retrieve a list of date strings ordered by date like this...

SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate
ORDER BY GeneratedDate

This orders by the varchar that I converted the dates to.

example...
02/01/2008
02/15/2008
02/21/2007
02/23/2007
02/29/2008

I have worked around this using an inline query...

SELECT CONVERT(Varchar(10), a.GeneratedDate, 101) AS GeneratedDate
FROM (SELECT DISTINCT Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101)) AS GeneratedDate
      FROM dbo.ProviderProcessGeneratedDate) a
ORDER BY a.GeneratedDate DESC

To get what I really want...
01/11/2008
01/04/2008
12/28/2007
12/21/2007

Is there an easier way? Seems like a lot of work to do something so simple.

Answer 1

The reason your first query gave a different order from what you wanted is...
- You use the field "GeneratedDate" to create your string
- You then alias that result field to "GeneratedDate"
- You then order by "GeneratedDate" without specifying the table
- So the result field is being used for ordering

The simple fix is mentioned in other answers...

ORDER BY ProviderProcessGeneratedDate.GenerateDate

By specifying the table there is no confusion and you get teh results you wanted.

[aside]

As a rule I always prefix my fields with [table]. to avoid any ambiguity. Especially since I often come back later and add in a join, forcing the ned for the tabel name.

Also, I alias the table names. Not to things like [a], but something meaningful like [Dates]. This shortens the query, but also allows me to change the table being used without having to change other references to it in other parts of the query.

[end of aside]

EDIT:

I've left my previous answer by way of humbling myself. I really should get a home sql server so I can try my answer before I post my answer... ***Apologies*

As the comment states, you may not specify something in the ORDER BY if it's not in the SELECT DISTINCT.

Therefore I would try GROUP BY instead...

SELECT
    Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101))
FROM
    ProviderProcessGeneratedDate
GROUP BY
    GeneratedDate
ORDER BY
    GeneratedDate

This assumes GeneratedDate is 1:1 with your CONVERT formula. If, for example, you have a TIME in your GeneratedDate fields, but your Date Format in CONVERT does not; you need to strip out the time from the GeneratedDate Field...

SELECT
    Convert(DATETIME,CONVERT(Varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0), 101))
FROM
    ProviderProcessGeneratedDate
GROUP BY
    DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0)
ORDER BY
    DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0)