How to select 1 last row for each id at mysql8 database?

Question

i am using mysql8 server. Have such table and want to receive 1 last row for each good_id.

CREATE TABLE good_states (`ID` int, `good_id` int, `sales` int);
    
INSERT INTO good_states (`id`, `good_id`, `sales`)
VALUES
    (1, 25, 33),
    (2, 25, 34),
    (3, 24, 14),
    (4, 25, 64),
    (5, 24, 53),
    (6, 24, 457),
    (7, 35, 96)
;

http://www.sqlfiddle.com/#!9/735bb60/1

This query

SELECT * FROM good_states  WHERE `good_id` IN (25,24) GROUP BY good_id

return to me first row from good_states for each good_id, but i need to receive last row for each good_id.

3 | 24 | 14
1 | 25 | 33

I want to receive something like this

6 | 24 | 427
4 | 25 | 64

ORDER by id DESC - just don't work here. As GROUP BY sort answer in ASC way. Any way to do what i want without GROUP by?

Pay attention that the demo is on the version 5.6 . You can move to a fiddle with version 8. — Barbaros Özhan, Oct 17 '20 at 15:05

score 2 · Accepted Answer · answered Oct 17 '20 at 14:58

You can use ROW_NUMBER() Analytic function :

SELECT * FROM
(
SELECT g.*,
       ROW_NUMBER() OVER (PARTITION BY good_id ORDER BY id DESC) AS rn
  FROM good_states g  
 WHERE `good_id` IN (25,24) 
) q
WHERE rn = 1

Demo

in order to return last row whenever ordered by id(ORDER BY id DESC) grouped by good_id values(PARTITION BY good_id)

score 0 · Answer 2 · answered Oct 17 '20 at 14:57

0

You could try using an inner join on max id grouped by good_id

select g.* from good_states g
inner join (
  SELECT max(id) max_id, good_id 
  FROM good_states 
  group by good_id
) t on t.nax_id = g.id
  order by g.id desc

answered Oct 17 '20 at 14:57

ScaisEdge

131,976
10
91
107

How to select 1 last row for each id at mysql8 database?

2 Answers2