Use query results in another sql query

Question

please help as this is turning into a week of almost no sleep already..

how can I get the ID and category from the LAST PRODUCT ADDED IN EACH SUBCATEGORY in this table?

so far this kinda works but it only returns the subcategory, and I also need the category and ID

$sql = mysql_query("SELECT DISTINCT subcategory FROM products ORDER BY id DESC LIMIT 12");

If i try something like

$sql = mysql_query("SELECT DISTINCT id, category, subcategory FROM products ORDER BY id DESC LIMIT 12");

it just returns everything..

Thank you all

Have a sub-query that returns last product for each sub-category. Join with that result. — jarlh
– jarlh, Commented Dec 5, 2016 at 8:27
don't use mysql_-functions, they are outdated, deprecated, and in PHP7 removed. use mysqli_ or PDO instead. — Franz Gleichmann
– Franz Gleichmann, Commented Dec 5, 2016 at 8:29
@NewbeeDev the first line of his post haha, he needs some sleep! — Jack Hales
– Jack Hales, Commented Dec 5, 2016 at 8:33

Beginner · Accepted Answer · 2016-12-05 09:02:56Z

1

First: group them by subcategory

GROUP  BY subcategory

then set your condition to id = (select your id of latest product according to its subcategory)

id = (SELECT id 
         FROM   products 
         WHERE  products.subcategory = Product.subcategory 
         ORDER  BY created DESC 
         LIMIT  1)

So your query would be like this

SELECT * 
    FROM   products Product 
    WHERE  id = (SELECT id 
                 FROM   products 
                 WHERE  products.subcategory = Product.subcategory 
                 ORDER  BY created DESC 
                 LIMIT  1) 
GROUP  BY subcategory

answered Dec 5, 2016 at 9:02

Beginner

4,1183 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ciprian Kis Over a year ago

Mate, what you have done here was way beyond my expectations :))) Thank you, you have saved me! you understood the entire problem better than anyone that I've asked before .. and the tables and everything.. pfff.. THANK YOU!!

viral barot · Accepted Answer · 2016-12-05 08:31:09Z

0

try this

SELECT * FROM `products` GROUP BY subcategory ORDER By id DESC

MySQL extension is deprecated and removed from php7 so use mysqli or PDO

answered Dec 5, 2016 at 8:31

viral barot

6314 silver badges8 bronze badges

Comments

Md. Sahadat Hossain · Accepted Answer · 2016-12-05 08:56:26Z

0

Change your query to

SELECT id, category, subcategory FROM products GROUP BY (subcategory) ORDER BY id DESC LIMIT 12

answered Dec 5, 2016 at 8:56

Md. Sahadat Hossain

3,2344 gold badges36 silver badges56 bronze badges

Comments

Yan.Zero · Accepted Answer · 2016-12-05 09:00:37Z

0

if id is AUTO_INCREMENT, you can do this:

SELECT * FROM `products` WHERE id IN ( SELECT MAX(id) FROM `products` GROUP BY subcategory)

answered Dec 5, 2016 at 9:00

Yan.Zero

4494 silver badges12 bronze badges

Collectives™ on Stack Overflow

Use query results in another sql query

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related