Q:

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.)

A:

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

