From the Data given below (Table : timeFramesDetail)
| tfgroup | City | activeDTTM | Begin_time | End_time | RankOfTime |
|---|---|---|---|---|---|
| 2 | 16 | 2021-04-05 02:30:03.510 | 2021-04-04 18:00:00.000 | 2021-04-05 06:00:00.000 | 1 |
| 2 | 16 | 2021-04-05 02:30:04.510 | 2021-04-04 18:00:00.000 | 2021-04-05 06:00:00.000 | 2 |
| 3 | 16 | 2021-04-06 02:30:04.667 | 2021-04-05 18:00:00.000 | 2021-04-06 06:00:00.000 | 1 |
| 3 | 16 | 2021-04-06 02:30:05.433 | 2021-04-05 18:00:00.000 | 2021-04-06 06:00:00.000 | 2 |
| 3 | 16 | 2021-04-06 02:30:10.777 | 2021-04-05 18:00:00.000 | 2021-04-06 06:00:00.000 | 3 |
| 3 | 16 | 2021-04-06 02:30:11.667 | 2021-04-05 18:00:00.000 | 2021-04-06 06:00:00.000 | 4 |
| 4 | 16 | 2021-04-07 03:27:31.977 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 1 |
| 4 | 16 | 2021-04-07 03:27:59.523 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 2 |
| 4 | 16 | 2021-04-07 03:28:30.283 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 3 |
| 4 | 16 | 2021-04-07 03:28:31.257 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 4 |
| 4 | 16 | 2021-04-07 03:28:35.617 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 5 |
| 4 | 16 | 2021-04-07 03:28:54.837 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 6 |
| 4 | 16 | 2021-04-07 03:29:35.807 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 7 |
| 4 | 16 | 2021-04-07 03:30:34.793 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 8 |
How to find the highest value rank of all tfGroups? Following should be the outcome :
| tfgroup | City | activeDTTM | Begin_time | End_time | RankOfTime |
|---|---|---|---|---|---|
| 2 | 16 | 2021-04-05 02:30:04.510 | 2021-04-04 18:00:00.000 | 2021-04-05 06:00:00.000 | 2 |
| 3 | 16 | 2021-04-06 02:30:11.667 | 2021-04-05 18:00:00.000 | 2021-04-06 06:00:00.000 | 4 |
| 4 | 16 | 2021-04-07 03:30:34.793 | 2021-04-06 18:00:00.000 | 2021-04-07 06:00:00.000 | 8 |
Following is the query I tried:
Select top 3 tfgroup ,City, activeDTTM
From timeFramesDetail
Group by tfgroup ,City, ActiveDTTM
This gives me accurate result for the first two for some reason and the rest is wrong. Can someone please help me with the rectification of the query?
CodePudding user response：
You can use a window function to partition and select the desired rank as follows:
select top (1) with ties *
from timeFramesDetail
order by row_number() over (partition by tfgroup order by RankOfTime desc)