CODEWITHSUNDEEP
mysqlsqlselectgreatest-n-per-group
Vill Raj
Vill Raj

Reputation: 215

SELECT query return 1 row from each group

This is a product table and have few million of records.

enter image description here

I want to list record as below:
Normally I use:

SELECT id, 
       product_name, 
       store_id 
FROM product
GROUP BY store_id 
ORDER BY id.


Currently having SQL performance issue. I need SQL query to output result like this.

enter image description here

Upvotes: 10

Views: 31689

Answers (3)

John Woo
John Woo

Reputation: 263693

There are many alternatives to solves this, one which I recommend is to have joined a subquery which separately gets the latest ID (assuming that the column is AUTO_INCREMENTed) for each store_ID.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  store_ID, MAX(ID) max_ID
            FROM    tableName
            GROUP BY store_ID
        ) b ON a.store_ID = b.store_ID AND
                a.ID = b.max_ID

for better performance, be sure to have an index on these columns: ID and store_id.

UPDATE 1

if you want to have limit for every records, use this below,

SELECT ID, product_Name, store_ID
FROM   tableName a
WHERE
  (
     SELECT COUNT(*) 
     FROM   tableName b
     WHERE  b.store_ID = a.store_ID AND b.ID >= a.ID
  ) <= 2;

Upvotes: 22

Anand thakkar
Anand thakkar

Reputation: 489

SELECT store_id,id,product_name FROM table_name
WHERE id IN (SELECT MAX(id) FROM table_name GROUP BY store_id)
ORDER BY id

this should work and you can Order by as per your req either by store_id or id.

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Try this please:

SELECT * FROM YOURTABLE B
JOIN (SELECT MAX(ID) MX FROM YOURTABLE GROUP BY STORE_ID) A
ON  A.STORE_ID = B.STORE_ID
AND B.ID = A.MX
GROUP BY B.STORE_ID
;

Upvotes: 1

Related Questions