SQL get all columns from max aggregation

Question

I have a table like This:

ID (Not PK)	time_to_prioritize	extra_info_1	extra_info_2
001	0	info_1	info_1
001	1	info_1	info_1
001	2	info_1_last	info_1_last
002	1	info_2	info_2
002	2	info_2_last	info_2_last
003	0	info_3_last	info_3_last

My objective is to get the max(time_to_prioritize) of all distinct ID's along with the extra columns, like this:

ID (Not PK)	time_to_prioritize	extra_info_1	extra_info_2
001	2	info_1_last	info_1_last
002	2	info_2_last	info_2_last
003	0	info_3_last	info_3_last

I got stuck at

SELECT TOP 1 * FROM my_table
ORDER BY time_to_prioritize DESC

I am trying to join it with itself, but with no results. What is the next step to achieve the result ? thanks.

P.S. the result on SQL MAX of multiple columns? does not help me, bc that link is the max of every column, I need the max of only 1 column, along with the rest of the data

Answer 1

You may use ROW_NUMBER function as the following:

SELECT T.ID, T.time_to_prioritize, T.extra_info_1, T.extra_info_2
FROM
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY time_to_prioritize DESC) rn
  FROM my_table
) T
WHERE T.rn=1
ORDER BY T.ID

See a demo.

Answer 2

Your approach with TOP 1 can be employed, though it requires some fixes.

If you want to get the first row with respect the ordering, you use TOP 1, though if you want to get the first row for each ID, then you require the clause TOP 1 WITH TIES, where the tie should happen to the ordering value. If you want to make your three "interesting rows" to be tied in the ordering, you should use the ROW_NUMBER window function inside the ORDER BY clause as follows:

SELECT TOP 1 WITH TIES * 
FROM my_table
ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY time_to_prioritize DESC) 

Check the demo here.

Answer 3

Try this:

SELECT TOP 1 MAX(time_to_prioritize) AS MAXtime_to_prioritize ,* FROM my_table
GROUP BY time_to_prioritize ,extra_info_1,  extra_info_2
ORDER BY time_to_prioritize DESC