Max of a column where ...

[Ike]

How can I select , for a given name, that record with the highest id for
something like:

id INTEGER(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
name VARCHAR(40)

I want to select the highest id where name='something '. I've tried along the
lines of:

select MAX(id) as id where name='something '

but with no success.

I know this is oversimplified -Ive stripped it down here intentionally to
try to determine how you select for a given row where a particular integer
value is the max value. Thanks, Ike

[Ike]

needs to have "Group by id"

"Ike" <rx*@hotmail.co m> wrote in message
news:I8******** ******@newsread 2.news.atl.eart hlink.net...

How can I select , for a given name, that record with the highest id for
something like:

id INTEGER(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
name VARCHAR(40)

I want to select the highest id where name='something '. I've tried along the lines of:

select MAX(id) as id where name='something '

but with no success.

I know this is oversimplified -Ive stripped it down here intentionally to
try to determine how you select for a given row where a particular integer
value is the max value. Thanks, Ike

[Michael Austin]

Ike wrote:

How can I select , for a given name, that record with the highest id for
something like:

id INTEGER(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
name VARCHAR(40)

I want to select the highest id where name='something '. I've tried along the
lines of:

select MAX(id) as id where name='something '

but with no success.

I know this is oversimplified -Ive stripped it down here intentionally to
try to determine how you select for a given row where a particular integer
value is the max value. Thanks, Ike

not sure of your "exact" syntax, understanding your data is paramount. It may
be giving you the correct information for what you asked...

Given:

mysql> select * from a;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | a |
| 2 | 2 | b |
| 3 | 2 | c |
| 4 | 2 | a |
| 5 | 2 | b |
| 6 | 2 | c |
| 7 | 2 | a |
| 8 | 2 | b |
| 9 | 2 | c |
| 10 | 2 | a |
| 11 | 2 | b |
| 12 | 2 | c |
+------+------+------+
12 rows in set (0.02 sec)

mysql> select max(a) from a where c='b';
+--------+
| max(a) |
+--------+
| 11 |
+--------+
1 row in set (0.00 sec)

mysql> select b,max(a) from a where c='b' group by b;
+------+--------+
| b | max(a) |
+------+--------+
| 2 | 11 |
+------+--------+
1 row in set (0.01 sec)

mysql> select b,max(a) from a where c='b';

ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),CO UNT()...) with no GROUP
columns is illegal if there is no GROUP BY clause

You only need a GROUP BY statement if you are selecting other columns in
addition to the "functioned " column

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)