Using mysql i need to retrieve which are inserted last in every category.

Question

Hi,

My prob in brief:

I have two tables namely category and product.

table: category

id     category

1      cat1

2      cat2

3      cat3

4      cat4

table: product

id  productName category

1    test1         1

2    test2         2

3    test3         3

4    test4         4

5    test5         2

6    test6         2

My prob is:

I need products which are inserted last in every category.

How to solve this.

thanks in advance

Answer 1

This is a variation of one of the most-common SQL questions asked here, the per-group maximum. See eg. this question for a variety of approaches.

The one I often use is a null-self-left-join, to select the row which has no value above it:

SELECT p0.*
FROM product AS p0
LEFT JOIN product AS p1 ON p1.category=p0.category AND p1.id>p0.id
WHERE p1.id IS NULL

This is assuming that id​s are allocated in order so the highest is the most recent. Normally it wouldn't be a good idea to rely on identity as an ordering mechanism; you'd typically add an added timestamp to each row to work on instead.

(Note this and many other per-group maximum functions can return more than one row when two rows have identical order columns. If this is a problem it could be avoided by using a UNIQUE ordering column; as a primary key id is already that. You can get a single row even when there are two maxima using SQL:2003's useful but rather ugly ROW_NUMBER() OVER windowing functions, but this is not supported by MySQL.)

Answer 2

You could add a create_time timestamp when a new a product has been added, and retrieve the newest by category be something like:

select max(create_time),category from product group by category