CODEWITHSUNDEEP
sqlsql-serversortingsql-order-by
Matias
Matias

Reputation: 71

sql server order by date and priority

Hello i have this data in my table 

ID|priority|lastModifiedDate
1 |   3    |2020-03-10 14:27:25.107
2 |   2    |2020-03-10 14:26:30.247
3 |   3    |2020-03-11 07:37:27.437
4 |   2    |2020-03-11 09:07:28.863

i am trying to ordering this data , first by lastModifiedDate desc and then by priority, to get a list like this

    ID|priority|lastModifiedDate
    3 |   3    |2020-03-11 07:37:27.437
    4 |   2    |2020-03-11 09:07:28.863
    1 |   3    |2020-03-10 14:27:25.107
    2 |   2    |2020-03-10 14:26:30.247

i am using the clause 

SELECT 
  [Id]
  ,[Priority] 
  ,[LastModifiedDate]

  FROM [News] order by LastModifiedDate desc, Priority desc

But I get this 

    ID|priority|lastModifiedDate
    4 |   2    |2020-03-11 09:07:28.863
    3 |   3    |2020-03-11 07:37:27.437
    1 |   3    |2020-03-10 14:27:25.107
    2 |   2    |2020-03-10 14:26:30.247

and if i try order first the column 'priority' and then lastmodifieddate, it dosen't work either, i assume this is because when it order the second time is in based on the result for the first sorted data,

So my question is, ¿is any way to get that result?

Upvotes: 0

Views: 1100

Answers (1)

Dan Schultz
Dan Schultz

Reputation: 71

You need to cast your order by LastModifiedDate as a date to remove the time element from your ordering.

Updated to handle the case where you have multiple records with the same priority on the same day, and want those ordered by the time.

SELECT 
  [Id]
  ,[Priority] 
  ,[LastModifiedDate]

  FROM [News] order by CAST(LastModifiedDate AS DATE) desc, Priority desc, LastModifiedDate

Upvotes: 3

Related Questions