0

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?

Improve this question
2
  • Are you using MySQL or MS SQL Server? (The answer will not be the same...) Commented Mar 11, 2020 at 13:44
  • Sorry i am using SQL Server Commented Mar 11, 2020 at 13:46

1 Answer 1

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

but if i want to use the date and time to order because i have ID|priority|lastModifiedDate 4 | 3 |2020-03-11 09:07:28.863 3 | 3 |2020-03-11 09:37:27.437 i want first the id 3 and then the 4
Not sure I'm following. If you use the entire datetime you get the result you said was incorrect as it's taking the time into account. 4 would come first since it has a newer time than 3. Priority would only matter with the datetime if you have two records with the exact same LastModifiedDate (including time). Based on your sample data you seem to want it based only on the date, then the priority, which is what the query above will handle.
mm if i have something like this ID|priority|lastModifiedDate 1 | 2 |2020-03-11 09:07:28.863 2 | 3 |2020-03-11 07:37:27.437 3 | 2 |2020-03-10 14:27:25.107 4 | 3 |2020-03-10 14:26:30.247 5 | 1 |2020-03-10 14:46:30.247 6 | 1 |2020-03-10 09:26:30.247
i want use the entire time to order like this ` ID|priority|lastModifiedDate 2 | 3 |2020-03-11 07:37:27.437 1 | 2 |2020-03-11 09:07:28.863 4 | 3 |2020-03-10 14:26:30.247 3 | 2 |2020-03-10 14:27:25.107 5 | 1 |2020-03-10 14:46:30.247 6 | 1 |2020-03-10 09:26:30.247` i suppose i had to sort first by date then by priority and then by time
Yes, if you have two records with the same priority, and want it to go date, priority, time, that's what you'd need to do. You can just add CAST(LastModifiedDate AS TIME) desc to the end of the query above. Edit: You actually wouldn't even need to cast to just the time, just ordering by LastModifiedDate desc at the end would handle it, as long as you're first ordering by just the date, then the priority.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.