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?
CodePudding user response：
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