Using mysql i need to retrieve which are inserted last in every category
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 开发者_StackOverflowis:
I need products which are inserted last in every category.
How to solve this.
thanks in advance
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
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.)
精彩评论