[Resolved] Need help with mySQL Query...

paulg

Hi All,
I need to select the single latest book added to the daabase for EACH author.

For the sake of ease, let's suppose my data is all in one table that looks like this :-
book_id int(6),
title varchar(255),
author varchar(255),
date_added date,
... etc.

Whats the query to only select the latest book for each author??

Probably easy - but my brain just ain't seeing it 😕

bastien

try

select author, max(datefield) from table group by author

paulg

Noop! Doesn't show the last title for each author.

Roger_Ramjet

Because the query is not selecting the title, only the author. You can do it in one easy query in mysql, because of it's slack sql syntax, but the following would not work with most other rdbms

SELECT title, author, MAX(id) FROM table
GROUP BY author

// assuming id is autoinc field

// or 

select title, author, max(datefield) from table group by author

paulg

Nah ... this gives the max id for each group BUT shows the first (oldest) title.

It only displays the latest id (or date) for the author.

😕

bastien

what version of mysql?

paulg

mysql version 3.23

bastien

try

SELECT title, author, MAX(id) FROM table
GROUP BY author order by title desc

paulg

Just the same, except the order. Still shows the first title for each author but with the latest date.

Roger_Ramjet

And 3.2 does not support subqueries either. Best option, upgrade to mysql 4.1.

Try this. I'm not sure it will work but it just might. Be interesting to see if it does.

SELECT DISTINCT t1.title, t1.author, t1.datefield, MAX(t2.id) 
FROM table AS t1 INNER JOIN table AS t2 ON  t1.id=MAX(t2.id) 
GROUP BY t2.author

You may have to tinker with the join to get it to work. If not then you will have to use 2 queries and a temp table. 1 to get the ids you want and the 2nd to get the details for the ids.

paulg

Cheers for that. I've gone the 2 queries route and this works fine.

Thanks to all for your help.😃