CODEWITHSUNDEEP
sqlsql-server
Haven Lin
Haven Lin

Reputation: 176

How to customize the sort when a column appears twice with the same value in SQL Server

Current table :

Id name    statusId  CreateDate    
--------------------------------------------
1  "Test"  2         2018-09-21 01:40:14.950
1  "Test"  8         2018-09-21 01:40:01.603
2  "Test"  2         2018-09-21 02:29:55.403
1  "Test"  2         2018-09-21 01:36:59.383
3  "Test"  3         2018-09-21 01:40:22.707
4  "Test"  8         2018-09-21 01:10:11.630
1  "Test"  3         2018-09-21 01:40:16.707
1  "Test"  8         2018-09-21 01:40:16.630
2  "Test"  2         2018-09-21 01:20:15.950
2  "Test"  8         2018-09-21 01:30:02.603
1  "Test"  2         2018-09-21 02:19:55.403
3  "Test"  2         2018-09-21 01:56:59.383

Want to find:

1  "Test"  2         2018-09-21 02:19:55.403
1  "Test"  3         2018-09-21 01:40:16.707
1  "Test"  8         2018-09-21 01:40:16.630
1  "Test"  2         2018-09-21 01:40:14.950
1  "Test"  8         2018-09-21 01:40:01.603
1  "Test"  2         2018-09-21 01:36:59.383

How to sort statusId as fixed order(2-3-8-2-8-2) and CreateDate DESC?

I try to use this SQL statement, but it does not return my expected results:

select id 
from table
where statusId = 8
   or statusId = 3
   or statusId = 2
order by 
    createDate desc, 
    case 
       when statusId = 3 then 1
       when statusId = 8 then 2
       when statusId = 2 then 3
    end asc

Upvotes: 0

Views: 81

Answers (3)

Haven Lin
Haven Lin

Reputation: 176

I found the solution as below, it meets my expected. 

select * from MyTable t1
join MyTable t2
    on t1.ID = t2.ID
join MyTable t3
    on t1.ID = t3.ID
join MyTable t4
    on t1.ID = t4.ID
join MyTable t5
    on t1.ID = t5.ID
where t1.statusID = 3
    and t2.statusID = 8
    and t3.statusID = 2
    and t4.statusID = 8
    and t5.statusID = 2
    and t1.CreateDate > t2.CreateDate
    and t2.CreateDate > t3.CreateDate
    and t3.CreateDate > t4.CreateDate
    and t4.CreateDate > t5.CreateDate
    and DATEDIFF(minute, t2.CreateDate, t1.CreateDate) < 1 
    and DATEDIFF(minute, t3.CreateDate, t2.CreateDate) < 1
    and DATEDIFF(minute, t4.CreateDate, t3.CreateDate) < 1
    and DATEDIFF(minute, t5.CreateDate, t4.CreateDate) < 10
order by t1.ID, t1.CreateDate desc

Upvotes: 0

David Dubois
David Dubois

Reputation: 3932

I think you might just have your sorts backwards.

The way you have it now, you are sorting by CreateDate. The second clause of the sort by is there to break any ties. It's saying "If there are multiple rows with the same CreateDate value, then sort those by the case expression." Since all your CreateDate values are unique, the second sort clause is ignored. There are no ties to break.

create table MyTable ( ID         int, 
                       Name       varchar(10),
                       StatusID   int,
                       CreateDate datetime )

insert into MyTable ( ID, Name, StatusID, CreateDate ) values
( 1, 'Ella',    2, '2018-09-21T01:40:14.950' ),
( 1, 'Spencer', 8, '2018-09-21T01:40:01.603' ),
( 2, 'Daisy',   2, '2018-09-21T02:29:55.403' ),
( 1, 'Chloe',   2, '2018-09-21T01:36:59.383' ),
( 3, 'Ivy',     3, '2018-09-21T01:40:22.707' ),
( 4, 'Alyssia', 8, '2018-09-21T01:10:11.630' ),
( 1, 'Hallie',  3, '2018-09-21T01:40:16.707' ),
( 1, 'Carole',  8, '2018-09-21T01:40:16.630' ),
( 2, 'Blake',   2, '2018-09-21T01:20:15.950' ),
( 2, 'Mary',    8, '2018-09-21T01:30:02.603' ),
( 1, 'Vivian',  2, '2018-09-21T02:19:55.403' ),
( 3, 'Theresa', 2, '2018-09-21T01:56:59.383' )

select ID, Name, StatusID , CreateDate
from MyTable
where StatusID in ( 2, 3, 8 )
order by

  case
    when StatusID = 3 then 1
    when StatusID = 8 then 2
    when StatusID = 2 then 3
  end asc,

  CreateDate desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I think you can accomplish what you want with row_number():

select . . .   -- it seems strange to select id because that is always "1"
from table
where statusId in (2, 3, 8)
order by row_number() over (partition by statusId order by createdDate desc),
         (case when statusId = 3 then 1
               when statusId = 8 then 2
               when statusId = 2 then 3
          end) asc

Upvotes: 1

Related Questions