SELECT query return 1 row from each group

by

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;

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. What is better subquery vs literal on IN clause in mysql
  3. SQL select only rows with max value on a column [duplicate]
  4. ROW_NUMBER() in MySQL
  5. Get top n records for each group of grouped results
  6. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  7. SQL query return data from multiple tables
  8. Get records with max value for each group of grouped SQL results
  9. quick selection of a random row from a large table in mysql
  10. MySQL SELECT only not null values
  11. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  12. Get the latest date from grouped MySQL data
  13. In SQL, how to select the top 2 rows for each group
  14. Get most recent row for given ID
  15. Using ORDER BY and GROUP BY together
  16. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  17. MySQL INSERT INTO … VALUES and SELECT
  18. Mysql select distinct
  19. MySQL error 1241: Operand should contain 1 column(s)
  20. How to select one row randomly taking into account a weight?
  21. quick selection of a random row from a large table in mysql
  22. Compare only day and month with date field in mysql
  23. Sum results of a few queries and then find top 5 in SQL
  24. Syntax error when using except in a query
  25. MySQL INNER JOIN select only one row from second table
  26. MySQL Select Query – Get only first 10 characters of a value
  27. SQL query to check if a name begins and ends with a vowel
  28. How to select data where a field has a min value in MySQL?
  29. How to SELECT by MAX(date)?
  30. How do I limit the number of rows per field value in SQL?

Leave a Comment