CODEWITHSUNDEEP
phpmysqlsql
Ciprian Kis
Ciprian Kis

Reputation: 89

Use query results in another sql query

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

I have a table called Products, with these columns: id | productName | category | subcategory | date_added | price .

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

Upvotes: 0

Views: 55

Answers (4)

Beginner
Beginner

Reputation: 4153

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

enter image description here enter image description here

Upvotes: 1

Yan.Zero
Yan.Zero

Reputation: 449

if id is AUTO_INCREMENT, you can do this:

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

Upvotes: 0

Md. Sahadat Hossain
Md. Sahadat Hossain

Reputation: 3236

Change your query to

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

Upvotes: 0

viral barot
viral barot

Reputation: 631

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

Upvotes: 0

Related Questions