SQL select row where id=max(id)

Question

I have a table which holds product information with multiple instances of a product with the same title, identified by different colours and their ids. I need to output the entire row where the id = the maximum id grouped by the title, but I can't seem to get it to do this. Here is a very simplified table and some example data:

id   title   colour   description

1    rico    red      blah1
2    rico    blue     blah2
3    rico    yellow   blah3
4    katia   black    blah4
5    katia   white    blah5

In this example with my code, I get 1 rico red blah1 when I want 3 rico yellow blah3.

Here is the code I am using:

SELECT pd_id, pd_title, pd_description, pd_colour,
       pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
WHERE 
  cat_sub_id_1 = '".$cat_sub_id."' 
  AND cat_parent_id='".$cat_parent_id."' 
GROUP BY pd_title 
HAVING MAX(pd_id) 
ORDER BY pd_id DESC

UPDATE: Thanks guys,

I used alinoz's answer to come up with the following code which works :)

SELECT
    pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
    SELECT max(pd_id)
    FROM product
    WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
    GROUP BY pd_title
    )
GROUP BY pd_title
ORDER BY pd_id DESC

Answer 1

Aaahhh, the good old greatest-n-per-group...

select *
from YourTable yt
inner join(
    select title, max(id) id
    from YourTable
    group by title
) ss on yt.id = ss.id and yt.title = ss.title

Of course, you should adapt this to your needs accordingly.

Also, I think this is a "must read": SQL Select only rows with Max Value on a Column

Answer 2

I think this might work (not tested):

SELECT * FROM that_table WHERE id IN (
SELECT MAX(id) FROM that_table GROUP BY title
) /* AND this = that */