Search code examples
mysqlsqlsql-order-by

SQL Arrange blocks of rows in Order By

Say I have an original output which looks like this (sorted purely by StartTime):

Name OrderNo OpNo StartTime Length
A 17345 op10 27/4/22 08:00 04:00:00
B 12727 op 20 27/4/22 11:00 02:00:00
A 18001 op10 27/4/22 13:20 01:00:00
B 17345 op 20 28/4/22 10:17 06:00:00
B 18001 op 20 29/4/22 13:00 04:00:00
C 17345 op 30 3/5/22 16:20 08:00:00
C 18001 op 30 5/5/22 13:00 09:00:00

How would it be possible to order the rows by OrderNo, then by OpNo, and then by the StartTime of the lowest OpNo in each OrderNo? So it would look like this:

Name OrderNo OpNo StartTime Length
A 17345 op10 27/4/22 08:00 04:00:00
B 17345 op 20 28/4/22 10:17 06:00:00
C 17345 op 30 3/5/22 16:20 08:00:00
B 12727 op 20 27/4/22 11:00 02:00:00
A 18001 op10 27/4/22 13:20 01:00:00
B 18001 op 20 29/4/22 13:00 04:00:00
C 18001 op 30 5/5/22 13:00 09:00:00

I understand it's easy enough to do the Order By OrderNo, OpNo to get the rows sorted into 'blocks' of each OrderNo with asc (or desc) OpNo. But the problem I'm then having is how do I order each of these 'blocks' by the StartTime of the lowest OpNo in each of these 'blocks'?

Hopefully I've made sense?

Solution

  • Using first_value

    select *
from (
    select *, first_value(StartTime) over(partition by OrderNo order by OpNo) blockStart    
    from mytable
) t
order by blockStart, OrderNo, OpNo