MySQL - I need to SELECT the highest umber in a column when there are multiple ID's

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.

Show us what you have tried, Stack Overflow is not a free code writing service. — Epodax, Feb 14 '17 at 10:38
have you search in Stack or tried yourself before posting your queries at there? — Manish, Feb 14 '17 at 10:41
Possible duplicate of [MySQL: Count occurrences of distinct values](http://stackoverflow.com/questions/1346345/mysql-count-occurrences-of-distinct-values) — Eric Lecoutre, Feb 14 '17 at 11:27
This question has been asked and answered a thousand times already. Next time, please undertake at least the most basic level of research before asking a question - e.g. consulting the manual and/or google. — Strawberry, Feb 14 '17 at 11:27

Jefferson Costa · Answer 1 · 2017-02-14T11:05:30.387

3

SELECT id, MAX(Number) as Number FROM mytable GROUP BY id

edited Feb 14 '17 at 11:05

answered Feb 14 '17 at 10:42

Jefferson Costa

@Aaqib1 please mark the answer as accepted answer so that people won't come back and answer again – Masivuye Cokile Feb 14 '17 at 10:54

Ayyoub · Accepted Answer · 2017-02-14T10:59:58.313

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.

score 0 · Answer 3 · edited Feb 14 '17 at 11:22

0

Most of time the id field is incremental but for your case you can use.

SELECT MAX(number) FROM `user` GROUP BY id

Where number is the column name from which you want to find MAX, and user is your table name.

edited Feb 14 '17 at 11:22

Labu

answered Feb 14 '17 at 11:04

sagar676

3 Answers3