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

  SELECT DISTINCT Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101)) 
                     AS GeneratedDate,
        A.GeneratedDate OrderByDate       
  FROM dbo.ProviderProcessGeneratedDate A
  Order By A.GeneratedDate Desc

Answer 2

I think you can use:

ORDER BY dbo.ProviderProcessGeneratedDate.GeneratedDate

to force it to use the value from the original table, instead of your new modified value? You could even add an alias to the FROM clause:

FROM dbo.ProviderProcessGeneratedDate ppgd

So that you could use the "ppgd" alias instead of the whole tablename in my first statement.

Answer 3

Perhaps you just need to use another field alias so that it uses the date for the ordering rather than the formatted string? I.e.

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

Although in most cases you should really be leaving the date as a date and doing any required formatting in the UI at the last moment possible.

Answer 4

Just to add to answers above...

You can convert back to string outside of tsql. Just return datetime type and convert to a date format you want in code (display layer).

Answer 5

Modify the ORDER BY of your original statement to use a sortable date string:

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

Answer 6

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)

Answer 7

You can also use CTE's if you're using SQL 2005 or 2008.

your code would look like:

WITH Dates(GeneratedDate) AS
(
SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate
)

SELECT GeneratedDate FROM Dates ORDER BY GeneratedDate

Answer 8

select * from
(
    SELECT DISTINCT CONVERT(varchar, GeneratedDate, 101) AS GeneratedDate
    FROM ProviderProcessGeneratedDate
    -- no need for ORDER BY, it is implicitly performed when DISTINCTing rows
) as y
order by convert(datetime, GeneratedDate) DESC

Answer 9

Use GROUP BY instead of DISTINCT:

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