SELECT query return 1 row from each group

Question

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

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.

Answer 1

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

• SQLFiddle Demo

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;

• SQLFiddle Demo