MySQL - 当有多个ID时，我需要选择列中的最大数字

Question

Take the following dataset:

id | Number
 1 | 6534
 1 | 765
 1 | 1234
 2 | 744
 2 | 6109
 3 | 333
 3 | 9888
 3 | 3112
 3 | 98432

I want to show the highest Number for each id.

So Like this:

id | Number
 1 | 6534
 2 | 6109
 3 | 98432

How can I do this with a SELECT statement?

I've already tried the following:

SELECT * FROM mytable ORDER BY id, Number Desc

But this shows the entire dataset.

I'm not trying to get the number of occurences. I am trying to get the highest Number grouped by id but can't get it to work.

Answer 1

You can try :

Select id,max(Number) from Your_Table group by id

Your Query that you have tried, it will only order your Data table by the given parameters.

In the meantime, What i have proposed to you, will select the two columns you want to diplay (the id, and the maximum of the column "Number").

And the group by will help to the maximum of each group. That's why a group by id is the right clause to have the maximum of each group of Ids.