[RESOLVED] Using ORDER BY with GROUP BY

[dbykel]

How can I group a column and then order those rows by another column?

Table:

+------+-----+
| data |number|
+------+-----+
| AAA | 2000 |
| AAA | 3000 |
| BBB | 2001 |
| AAA | 1000 |
| BBB | 1001 |
| CCC | 1002 |
+------+-----+

Query:

SELECT data,number FROM table GROUP BY data ORDER BY number DESC

Why does this query not produce the following?

AAA 3000
BBB 2001
CCC 1002

I want to group the data and then find the biggest number

Thanks in advance for any help

[Weedpacket]

Well, here's a question:

+------+-----+
| data |number|
+------+-----+
| AAA | 2000 |
| AAA | 3000 |
+------+-----+

You have two records with the same value for "data", but different values for "number".

You ask it why the query doesn't give you a record "AAA 3000".

But why don't you ask it why the query doesn't give you a record "AAA 2000" instead? How is it supposed to know which one you want? Which one do you want?

dbykel wrote:

I want to group the data and then find the biggest number

Then you should say so.

SELECT data, max(number) FROM table GROUP BY data ORDER BY max(number) DESC

[dbykel]

That is exactly what I am looking for.

Thanks so much!