mysql - Select unique records limit with N rows

Question

I have database records shown below,

id  | dataId    | value
1   |    1      |   xxx 
2   |    1      |   xx1
3   |    1      |   xx2
4   |    1      |   xx1
5   |    2      |   yyy
6   |    2      |   yy1
7   |    2      |   yy2 
8   |    1      |   zzz  
9   |    2      |   yy3  

My desired result would be something like this

id  | dataId    | value
8   |    1      |   zzz
4   |    1      |   xx1
3   |    1      |   xx2
9   |    2      |   yy3
7   |    2      |   yy2
6   |    2      |   yy1

I want to select N latest id per dataId where N in this case is 3

thanks in advance.

Answer 1

  DROP TABLE IF EXISTS my_table;

  CREATE TABLE my_table
  (id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,dataId INT NOT NULL    
  ,value VARCHAR(12) NOT NULL
  );

  INSERT INTO my_table VALUES
  (1   ,1      ,'xxx'),
  (2   ,1      ,'xx1'),
  (3   ,1      ,'xx2'),
  (4   ,1      ,'xx1'),
  (5   ,2      ,'yyy'),
  (6   ,2      ,'yy1'),
  (7   ,2      ,'yy2'),
  (8   ,1      ,'zzz'),
  (9   ,2      ,'yy3'); 

  SELECT x.* 
    FROM my_table x 
    JOIN my_table y  
      ON y.dataid = x.dataid 
     AND y.id >= x.id 
   GROUP 
      BY dataid
       , id 
  HAVING COUNT(*) <= 3 
   ORDER 
      BY dataid
       , id DESC;
  +----+--------+-------+
  | id | dataId | value |
  +----+--------+-------+
  |  8 |      1 | zzz   |
  |  4 |      1 | xx1   |
  |  3 |      1 | xx2   |
  |  9 |      2 | yy3   |
  |  7 |      2 | yy2   |
  |  6 |      2 | yy1   |
  +----+--------+-------+
  6 rows in set (0.03 sec)

  mysql>