question

JoeHaky avatar image
JoeHaky asked

ORDER BY merged columns

Hello Experts!

Please consider table dt:

Create Table dt 
(LogID Int Not Null Identity Primary Key, StartTime DateTime, EndTime DateTime)


Insert Into dt(StartTime,EndTime)
Values     
('2019-07-31 07:00', '2019-07-31 7:30'),    
('2019-07-31 06:30', '2019-07-31 07:00'),    
('2019-07-31 12:00', '2019-07-31 13:15'),    
('2019-07-31 7:30', '2019-07-31 12:00'),    
('2019-7-31 05:30', '2019-07-31 06:30')


┌───────┬──────────────────┬──────────────────┐
│ LOGID │    STARTTIME     │     ENDTIME      │
├───────┼──────────────────┼──────────────────┤
│     1 │ 2019-07-31 07:00 │ 2019-07-31 07:30 │
│     2 │ 2019-07-31 06:30 │ 2019-07-31 07:00 │
│     3 │ 2019-07-31 12:00 │ 2019-07-31 13:15 │
│     4 │ 2019-07-31 07:30 │ 2019-07-31 12:00 │
│     5 │ 2019-07-31 05:30 │ 2019-07-31 06:30 │
└───────┴──────────────────┴──────────────────┘


I need to come up with a query that will return

  • each record twice, once with StartTime and once with EndTime as EventTime
  • order by EventTime and then by StartTime first, EndTime second.

Desired result:

┌───────┬──────────────────┐
│ LOGID │    EVENTTIME     │
├───────┼──────────────────┤
│     5 │ 31/07/2019 5:30  │
│     5 │ 31/07/2019 6:30  │
│     2 │ 31/07/2019 6:30  │
│     2 │ 31/07/2019 7:00  │
│     1 │ 31/07/2019 7:00  │
│     1 │ 31/07/2019 7:30  │
│     4 │ 31/07/2019 7:30  │
│     4 │ 31/07/2019 12:00 │
│     3 │ 31/07/2019 12:00 │
│     3 │ 31/07/2019 13:15 │
└───────┴──────────────────┘


I have tried:

Select LogID, StartTime As EventTime, 'Start' As EventType from dt
Union All
Select LogID, EndTime, 'End' from dt
Order ByEventTime Asc, EventType Desc

But the the result is not in the correct order.

Thank you for your help!

sql-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think you need to explain what you want a little bit more - you said you wanted to order by EventTime, then StartTime, then EndTime - so for the 2nd and 3rd rows in that output where the EventTime is the same, then it is then ordering by StartTime (as LogID 5 started before LogID 2).

The query output doesn't match your expected output, but it does match your specification.

0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

Try

Select LogID, StartTime As EventTime, 'Start' As EventType, 
     StartTime as EventStartTime from dt
Union All
Select LogID, EndTime, 'End', StartTime as EventStartTime from dt
order by EventTime, EventStartTime


gives


LogID       EventTime               EventType EventStartTime
----------- ----------------------- --------- -----------------------
5           2019-07-31 05:30:00.000 Start     2019-07-31 05:30:00.000
5           2019-07-31 06:30:00.000 End       2019-07-31 05:30:00.000
2           2019-07-31 06:30:00.000 Start     2019-07-31 06:30:00.000
2           2019-07-31 07:00:00.000 End       2019-07-31 06:30:00.000
1           2019-07-31 07:00:00.000 Start     2019-07-31 07:00:00.000
1           2019-07-31 07:30:00.000 End       2019-07-31 07:00:00.000
4           2019-07-31 07:30:00.000 Start     2019-07-31 07:30:00.000
4           2019-07-31 12:00:00.000 End       2019-07-31 07:30:00.000
3           2019-07-31 12:00:00.000 Start     2019-07-31 12:00:00.000
3           2019-07-31 13:15:00.000 End       2019-07-31 12:00:00.000

(10 rows affected)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Perfect - works like a charm. Thank you @Kev Riley!

0 Likes 0 ·
JoeHaky avatar image
JoeHaky answered

Hi @Kev Riley, thank you for your response!

Please have a look at the comments to the right of the desired results below, which I think explains well how the required order would go:

Records are sorted by EventTime, but there are 2 instances of each record (1 with the StartTime as as EventTime, 1 with the EndTime as EventTime. The order should be a continuous StartTime/EndTime sequence.


┌───────┬──────────────────┐
│ LOGID │    EVENTTIME     │
├───────┼──────────────────┤
│     5 │ 31/07/2019 5:30  │ EventTime  = Log 5 StartTime
│     5 │ 31/07/2019 6:30  │ EventTime  = Log 5 EndTime
│     2 │ 31/07/2019 6:30  │ EventTime  = Log 2 StartTime
│     2 │ 31/07/2019 7:00  │ EventTime  = Log 2 EndTime
│     1 │ 31/07/2019 7:00  │ EventTime = Log 1 StartTime
│     1 │ 31/07/2019 7:30  │ EventTime = Log 1 EndTime
│     4 │ 31/07/2019 7:30  │ EventTime = Log 4 StartTime
│     4 │ 31/07/2019 12:00 │ EventTime = Log 4 EndTime
│     3 │ 31/07/2019 12:00 │ EventTime = Log 3 StartTime
│     3 │ 31/07/2019 13:15 │ EventTime = Log3 EndTime
└───────┴──────────────────┘


In my query I generate an extra column so that I can differentiate between StartTime and EndTime, but ordering on those without the LogID doesn't produce the right order. And I can't figure out how to include LogID in the Order By.


Please let me know if I should clarify further.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK. You've tagged as SQL2008 - is that correct?

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.