Max of a column where ...

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

Re: Max of a column where ...

needs to have "Group by id"

"Ike" wrote in message
news:I8nJf.188$5M6.35@newsread2.news.atl.earthlink.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
>
>

Re: Max of a column where ...

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(),COUNT()...) 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
:)