2

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
SChavan
  • 57
  • 5
  • Please post what you have tried, table structures, and more help on posting good question: https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question – Brad Sep 14 '21 at 19:20
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Sep 14 '21 at 23:33

1 Answers1

0

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)
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Hello Stu, This query gives me the output for rank as 1 for all the tfgroups. Can this be altered to give the output for the highest 'value' of the rankoftime for all the tfgroups ? – SChavan Sep 14 '21 at 19:40
  • I'm not sure I follow - can you elaborate, see this [example fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1ec80890b0f7ff77a215c94fc8f47e1a). I didn't both with all the columns as the solution is just selecting the rows for the highest rank of each group. Can you explain what is wrong/missing in the Fiddle? – Stu Sep 14 '21 at 19:44
  • 1
    Hello Stu, My apologies! I was looking at the wrong table. But this is absolutely right and makes the output flawless. Thank you so much for your kind help! :) – SChavan Sep 14 '21 at 19:51
  • `TOP 1 WITH TIES ... ORDER BY ROW_NUMBER` requires an extra sort, see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=88fe94997052ce502c9482078a7c2571 – Charlieface Sep 14 '21 at 23:35
  • 1
    While that's unavoidably true, I would say it's negligible; testing with 100k rows, the logical reads performed by both versions is identical. If I marked down every answer on SO because it wasn't the absolute most performant of all possible solutions I'd have no rep by now ;-) – Stu Sep 15 '21 at 06:26