1

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

Fero
  • 12,969
  • 46
  • 116
  • 157

2 Answers2

0

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

Community
  • 1
  • 1
bobince
  • 528,062
  • 107
  • 651
  • 834
0

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
stacker
  • 68,052
  • 28
  • 140
  • 210
  • `productName` can't be selected for `GROUP BY category` because the product name doesn't have a functional dependency on category. You will get an arbitrary product name from the category, not necessarily the same product as the one whose `create_time` matched `MAX(create_time)`. In other database software and ANSI SQL:92, this is simply an error. – bobince Apr 17 '10 at 10:56