SELECT rows with MAX id minus 1 with group by

user_17701310 0
Viewed 98

I have a table with id, city_id, and stock which looks like this.

id city_id stock
1 1 1000
2 2 500
3 3 11000
4 2 600
5 3 12000
6 1 2000
9 3 13000
10 1 3000
11 1 4000
12 2 700

To select the latest stock values for each city_id I used the following query, which works fine.

SELECT `stock` 
FROM `stock_table` 
WHERE id in ( SELECT MAX(id) 
              FROM `stock_table` 
              GROUP BY city_id 
             );

It returns

stock
13000
4000
700

Now I want to select 2nd latest stock entry for each city_id. So the output should be like the following table.

stock
12000
3000
600

Any help is greatly appreciated. Thanks!

mysql sql
2 Answers

In MySQL 8 you can use the row_number() window function to assign a number to each row ordered by the id per partition of city_id. Then just filter on that being 2 (in your example; you can use any number).

SELECT x.stock 
       FROM (SELECT s.stock,
                    row_number() OVER (PARTITION BY s.city_id
                                       ORDER BY s.id DESC) rn
                    FROM stock_table s) x
       WHERE x.rn = 2;
user_96614240

u can use group by with row number and get rownumber 2 ROW_NUMBER() OVER (PARTITION BY city_id ORDER By id) as ROW

user_59086460
Related