SELECT rows with MAX id minus 1 with group by-CodePudding

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!

CodePudding user response：

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;

CodePudding user response：

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