exebook exebook - 1 month ago 18
SQL Question

Get last item name on the shelf

I have this table:

CREATE TABLE stock (shelf varchar, item_id INT, item_name varchar);


Now I want to get a list of last items on each shelf. Last item is the item with the largest
item_id
. I know how to get only the last id:

SELECT shelf, MAX(item_id) FROM stock GROUP BY shelf;


Given this data:

INSERT INTO stock (shelf, item_id, item_name) VALUES (1, 1, 'one');
INSERT INTO stock (shelf, item_id, item_name) VALUES (2, 2, 'two');
INSERT INTO stock (shelf, item_id, item_name) VALUES (1, 3, 'three');
INSERT INTO stock (shelf, item_id, item_name) VALUES (2, 4, 'four');


The code above returns this:

2 | 4
1 | 3


But how do I get the last item's name as well?

(It is better to have a solution for the standard SQL, but if that is not possible right now I am using Postgres.)

Answer

Here is a Generic solution using Correlated sub-queryworks in every database engine I know

select * from stock s
where item_id = (SELECT  MAX(item_id) FROM stock s1 where s1.shelf = s.shelf)

Another way is using ROW_NUMBER which is efficient than correlated sub-query approach but does not work with all RDBMS ex : Mysql

Comments