0

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

Improve this question
1
  • something like '''Select Max (time) from my_table Groupby(ID) ''' ? My issue with this approach is that I cannot get the rest of the columns Commented Sep 22, 2022 at 13:47

3 Answers 3

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I ended up using this solution. it feels more readable to me. Thanks!
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.

Improve this answer

Comments

0

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
Improve this answer

1 Comment

This only works on MySQL with only_full_group_by set to off

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.