sql - How I create this mysql select query? -


i have mysql product table columns of id, name, description, , category_id.

this result when select id, name , category_id.

+----+-------------+----------------------------+ | id | category_id | name                       | +----+-------------+----------------------------+ |  6 |           1 | category name              | |  7 |           2 | category name              | |  8 |           3 | category name              | |  9 |           2 | category name              | | 11 |           2 | category name              | | 15 |           3 | category name              | | 13 |           4 | category name              | | 14 |           1 | category name              | | 15 |           2 | category name              | | 16 |           2 | category name              | | 17 |           3 | category name              | | 18 |           4 | category name              | | 19 |           1 | category name              | +----+-------------+----------------------------+ 

my question is, need select newly added 4 products above table. these 4 products should 4 different categories.

this how tried it. not working me.

select p.id          , p.category_id          , p.name          , p.description products p p.category_id in ('1', '2', '3', '4')  order added_date desc limit 4   +----+-------------+--------+-------------+ | id | category_id | name   | description | +----+-------------+--------+-------------+ |  8 |           4 | dfadff | dfasf       | |  7 |           4 | dffdsf | fdfdfaf     | |  6 |           3 | fdfdsf | fdsfdsfd    | |  5 |           2 | dffdsf | dfsfsf      | +----+-------------+--------+-------------+ 4 rows in set (0.00 sec) 

result above query:

you have first decide product "last" each category, can simple max(id) if id autoincrementing. pick products these ids:

select p.id, p.category_id, p.name products p join (   select max(id) id products group category_id ) tmp using(id); 

it can seen in action @ http://sqlfiddle.com/#!9/c86fa/2

if need check where p.category_id in ('1', '2', '3', '4') part, enough put inside subquery.


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -