1

I have a mysql database, in there I have 1 table. That table contains "id","Name","Type".

I want to get 10 rows from every type. For example:

( In this example I only want 1)

0 - test1 - a
1 - test2 - a
2 - test3 - b

Than this needs to select:

0 - test1 - a
2 - test3 - b

How can I do this in 1 SQL query? Or is that impossable?

Laurence
  • 1,815
  • 4
  • 22
  • 35
  • As far as I remember you always need two queries. – Mikhail Feb 17 '12 at 14:42
  • 1
    I don't think this is a duplicate -- the upvoted solution there mentions window functions (it's for SQL Server) -- and MySQL doesn't support these. – Matt Fenwick Feb 17 '12 at 14:50
  • Article: [How to select the first/least/max row per group in SQL](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) – ypercubeᵀᴹ Feb 17 '12 at 15:01
  • 1
    And this answer that works in MySQL (and in most DBMS): [How to SELECT the newest four items per category?](http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867) – ypercubeᵀᴹ Feb 17 '12 at 15:07
  • And a different approach, specific for MySQL, in @Quassnoi's blog: [Advanced row sampling](http://explainextended.com/2009/03/06/advanced-row-sampling/) – ypercubeᵀᴹ Feb 17 '12 at 15:10

1 Answers1

4

Try this query -

SELECT id, name, type
  FROM (
  SELECT t1.*, COUNT(*) cnt FROM table t1
    LEFT JOIN table t2
      ON t2.type = t1.type AND t2.id <= t1.id
    GROUP BY
      t1.type, t1.id
  ) t
WHERE cnt <= 10;
Devart
  • 119,203
  • 23
  • 166
  • 186