CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
rickyProgrammer
rickyProgrammer

Reputation: 1167

SQL Server Query - Multiple Order By column and per Same Grouping

Sample Table

enter image description here

I need to sort the record above by REMARKS in ascending order, and then by FIRSTDATE in descending order, and then by LASTDATE in descending order

I do this query

select * from tblRecord 
order by Remarks, FirstDate desc, Lastdate desc

And gives me this output

enter image description here

It should be like this:

enter image description here

Updated: Startdate and Enddate refer to the actual start date and last date operation of the tenants, What I want is to separate the sorting of tenant per remarks, when ceased, sorting should be based on firstdate in descending order, when started, should be per enddate in descending order.

Upvotes: 1

Views: 74

Answers (2)

sqluser
sqluser

Reputation: 5672

This probably help you using CASE in ORDER BY 

SELECT * FROM tblRecord 
ORDER BY CASE Remarks WHEN 'started' THEN Lastdate
                      ELSE FirstDate END
DESC

Upvotes: 2

Jeremy C.
Jeremy C.

Reputation: 2465

Not sure what it is you are looking for seeing as you aren't responding for the moment, I have 2 situations in which I can get the result that you are looking for, 1 of these will deffinitely be wrong or both could be wrong but they both produce the result you are looking for with the test data you provided

Situation 1: You need to order by startdate descending when your remarks is ceased and by startdate ascending when remarks is started in which case you would get this code: SQLFIDDLE

select * from mytable
order by remarks,
CASE WHEN remarks = 'started'
THEN startdate END ASC,
CASE WHEN remarks ='ceased'
THEN startdate END DESC,
enddate DESC

Situation 2: when you have remark ceased you need to order the records by startdate and when remark is started you need to order by enddate, in which case the query would be: SQLFIDDLE

select * from mytable
order by remarks,
CASE WHEN remarks = 'started'
THEN enddate END DESC,
CASE WHEN remarks ='ceased'
THEN startdate END DESC;

Upvotes: 1

Related Questions