Determine primary key in an aggregate query

#1

I am doing a GROUP BY to determine the MAX value in a GROUP.
How can I determine, with a query, the primary key of each row that has
the MAX value?
Please see example below.
Thank you,
Joe

mysql> describe MyTable;
+--------+---------+------+-----+---------+----------------+

Quote:

Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
Key | int(11) | NO | PRI | NULL | auto_increment |
Value1 | int(11) | NO | | NULL | |
Value2 | int(11) | NO | | NULL | |
+--------+---------+------+-----+---------+----------------+

mysql> select * from MyTable;
+-----+--------+--------+

Quote:

Key | Value1 | Value2 |
+-----+--------+--------+
1 | 1 | 2 |
2 | 1 | 3 |
3 | 1 | 4 |
4 | 10 | 8 |
5 | 10 | 2 |
6 | 10 | 12 |
+-----+--------+--------+

mysql> select Value1, MAX(Value2) from MyTable Group BY Value1;
+--------+-------------+

Quote:

Value1 | MAX(Value2) |
+--------+-------------+
1 | 4 | <== Key = 3, how can I determine this?
10 | 12 | <== Key = 6, how can I determine this?
+--------+-------------+

#2

El 15/06/2011 16:04, Joe Hesse escribió/wrote:

Quote:

I am doing a GROUP BY to determine the MAX value in a GROUP.
How can I determine, with a query, the primary key of each row that has
the MAX value?
Please see example below.
Thank you,
Joe

mysql> describe MyTable;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| Key | int(11) | NO | PRI | NULL | auto_increment |
| Value1 | int(11) | NO | | NULL | |
| Value2 | int(11) | NO | | NULL | |
+--------+---------+------+-----+---------+----------------+

mysql> select * from MyTable;
+-----+--------+--------+
| Key | Value1 | Value2 |
+-----+--------+--------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 4 |
| 4 | 10 | 8 |
| 5 | 10 | 2 |
| 6 | 10 | 12 |
+-----+--------+--------+

mysql> select Value1, MAX(Value2) from MyTable Group BY Value1;
+--------+-------------+
| Value1 | MAX(Value2) |
+--------+-------------+
| 1 | 4 |<== Key = 3, how can I determine this?
| 10 | 12 |<== Key = 6, how can I determine this?
+--------+-------------+

Good old Strawberry Query:

http://dev.mysql.com/doc/refman/5.0/...group-row.html

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#3

Joe Hesse <JoeHesse (AT) gmail (DOT) com> wrote:

Quote:

I am doing a GROUP BY to determine the MAX value in a GROUP.
How can I determine, with a query, the primary key of each row that has
the MAX value?

http://lmgtfy.com/?q=mysql+groupwise+maximum

XL

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Determine primary key in an aggregate query

comp.databases.mysql comp.databases.mysql

Determine primary key in an aggregate query - 06-15-2011 , 09:04 AM

Re: Determine primary key in an aggregate query - 06-15-2011 , 09:06 AM

Re: Determine primary key in an aggregate query - 06-15-2011 , 09:53 AM