MySQL Forums
Forum List  »  Newbie

A simple but nasty query ... (group by line order)
Posted by: Pedro Ribeiro
Date: August 12, 2004 07:54AM

I'm developing an application that uses MySQL, at this time I’m having trouble doing a query (involving multiple tables, etc) that I can resume to this simple problem:

One table with 3 columns named "a","b","c" with values (in this order):

mysql> select * from test;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 2 | 2 | 2 |
+---+---+---+

I want a query to return-me one line for each "a" existing, that line should be the one with the bigger value of "b". In this case:

+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 3 | 1 |
| 2 | 2 | 2 |
+---+---+---+

It seems simple no?
The problem is that sorting (in GROUP BY or ORDER BY) seems to apply only after grouping and the result I’m having is:
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 2 |
| 2 | 2 | 2 |
+---+---+---+

Any suggestion to a query that does it right?
How do I select witch line represents the group when using GROUP BY?
Thanks!

Options: ReplyQuote


Subject
Written By
Posted
A simple but nasty query ... (group by line order)
August 12, 2004 07:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.