Choose a unique write limit using N lines

Question

I have the database entries 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 will 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 the last identifier on dataId, where N in this case is 3

Thanks in advance.

Answer 1

Here is an interesting article that you can specify to get the selected number of elements from a group. This can be found from this question .

To get the last 3 identifiers for each dataid, you can use this query:

SELECT id, dataid, value, date
FROM myTable m
WHERE(
  SELECT COUNT(*) FROM myTable mt
  WHERE mt.dataid = m.dataid AND mt.id >= m.id
) <= 3;

, where id vlaues, 3. , WHERE mt.dataid = m.dataid - , .

, , . , UNION . . . :

(SELECT * FROM myTable WHERE dataid = 1 ORDER BY id DESC LIMIT 3)
UNION ALL
(SELECT * FROM myTable WHERE dataid = 2 ORDER BY id DESC LIMIT 3)

SQL Fiddle .

Answer 2

  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>

Answer 3

mysql : DISTINCT, : SELECT DICTINCT column FROM table .

LIMIT : SELECT x FROM y LIMIT number

- SELECT DISTINCT * FROM table LIMIT 10

: http://dev.mysql.com/doc/refman/5.0/en/select.html

, , , 10 .

sidenote: distinct can work on a specific column and on full lines, in its example it is only on full lines, use the mysql manual to learn more about this.

Answer 4

Are you trying to select the first single value? if so you could do

SELECT id, dataId, distinct(value), date
FROM table
ORDER BY date