help to structure query without using subselects

[Petre Agenbag]

Hi List

Can anyone help me to do the following without the need of subselects:
I have a table that contains rows where mostly, the only common field is
the "name". The rest of the data fields, even though they are related to
the "name", differs from row to row ( they are things like the address
etc, which could have changed on the next entry for the same "name")

What I'm trying to get out of the table is a list of all the names (
with the duplicates pruned out by DISTINCT) and other collumns for the
row with the highest id.

This works:

select distinct name, max(id) as mid from table group by name

but it only returns the name and the highest id for such a name.

if I try this:

select distinct name, max(id) as mid, anything_else from table group by
name

it returns the FIRST "anything_else" , and not the value in the last row
for that name ( as I would like it to)

Any help?

mysql, query

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw