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

The HAVING MAX(pd_id) doesn't do anything: you need to test this against something. — Jamie F, Oct 18 '11 at 15:13
Don't use alinoz's answer. Use Adrian's or Salman's (if the `id` is the Primary Key). — ypercubeᵀᴹ, Oct 18 '11 at 15:57

score 21 · Answer 1 · edited May 23 '17 at 10:31

21

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

edited May 23 '17 at 10:31

Community

1
1

answered Oct 18 '11 at 15:18

Adriano Carneiro

57,693
12
90
123

1

@ypercube Thanks. The thing is: this is asked so often (and I answer that so often!) that I decided to get one of my answers and re-write it as a comprehensive answer on the subject, to help others in the future. – Adriano Carneiro Oct 18 '11 at 15:25
It's an excellent answer. Feel free to add the approach (like in my answer here) that produces (slightly) different results when there are ties, e.g. 2 or more rows with maximum id. – ypercubeᵀᴹ Oct 18 '11 at 15:33

score 9 · Answer 2 · answered Oct 18 '11 at 15:13

9

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 */

answered Oct 18 '11 at 15:13

Salman A

262,204
82
430
521

DnD · Answer 3 · 2013-01-11T14:54:11.740

7

simply for one row by any (in this case 'id') max value

SELECT * FROM `your_table` order by id desc limit 1

edited Jan 11 '13 at 14:54

answered Nov 30 '12 at 19:11

DnD

183
2
7

alinoz · Accepted Answer · 2011-10-18T15:16:17.357

3

try to add one more clause to your where with pd_id = (select max(pd_id) from ... )

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."' 
  AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title

This query is not optimal but it would do the job.

edited Oct 18 '11 at 15:16

answered Oct 18 '11 at 15:09

alinoz

2,822
22
38

3

Won't work. You'd select one single max id in the subquery, which applies to only one product group, which makes the rest of the where clause exclude all other products except the one whose ID is highest in the whole table. – Marc B Oct 18 '11 at 15:20

ypercubeᵀᴹ · Answer 5 · 2011-10-18T15:41:16.610

2

Besides Adrian's and Salman's approach, there's alo this one which gives different results when there are ties, two or more rows with same (maximum) id. It will show only one row per title while the other query will show all of them (in your case that is probably irrelevant as you order by id, which I suppose is the Primary Key):

SELECT 
    t.* 
FROM 
    TableX AS t
  JOIN
    ( SELECT DISTINCT
          title                        --- what you want to Group By
      FROM TableX 
    ) AS dt
    ON t.PK =                          --- the Primary Key of the table
       ( SELECT tt.PK
         FROM TableX AS tt
         WHERE tt.title = dt.title
         ORDER BY id ASC               --- (or DESC) what you want to Order By
         LIMIT 1
       )

edited Oct 18 '11 at 15:41

answered Oct 18 '11 at 15:29

ypercubeᵀᴹ

113,259
19
174
235

1

+1 Very interesting... This only selects one max-value per group-identifier in the case of a "tie", which is desirable, depending on the specs. – Adriano Carneiro Oct 18 '11 at 15:39
1

Credits to Quassnoi: http://stackoverflow.com/questions/1201296/how-to-optimize-mysql-query-group-and-order/1201365#1201365 – ypercubeᵀᴹ Oct 18 '11 at 15:44
and: http://stackoverflow.com/questions/7233757/order-within-group-by/7234200#7234200 – ypercubeᵀᴹ Oct 18 '11 at 15:44
Just one thing: apparently, not all versions of MySQL accept LIMIT in subqueries http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html However, the approach will work for other DB specific FIRST n contruct – Adriano Carneiro Oct 18 '11 at 15:46
MySQL accepts `= (SELECT ... LIMIT 1)` (not sure which version and above). It does not accept `IN (SELECT ... LIMIT x)` or `> ANY (SELECT ... LIMIT x)` – ypercubeᵀᴹ Oct 18 '11 at 15:49
I see, interesting indeed. I'm fluent in MySQL, not not "natively fluent" :) – Adriano Carneiro Oct 18 '11 at 15:51

score 0 · Answer 6 · answered Jan 20 '16 at 13:01

I think, its not necessary to use subselects:

select 
SUBSTRING_INDEX(group_concat(testtab.ActionID order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as ActionIDs,
SUBSTRING_INDEX(group_concat(testtab.DiscountedPrice order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as DiscountedPrices
testtab.*
from testtab 
where 
testtab.StartDate <= 20160120 
and testtab.EndDate > 20160120
and testtab.VariantID = 302304364
and testtab.DeleteStatus = 0
group by testtab.VariantID
order by testtab.VariantID, testtab.ActionID;

Its just a fast Example from my tests. Just by shure to order in the same way in the substring_indexes und use a good separator.

Have fun

score 0 · Answer 7 · answered Oct 18 '11 at 15:17

since you are already ordering, you can use row number =1 in where condition. see this link. and since your db is not known. I cant give a specific query but direct you in the direction

or try sql top which will yield the same result, as order by is used

SQL select row where id=max(id)

7 Answers7