CODEWITHSUNDEEP
mysql
prix
prix

Reputation: 143

Select unique records limit with N rows

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.

Upvotes: 0

Views: 189

Answers (4)

AdamMc331
AdamMc331

Reputation: 16691

Here is an interesting article you can reference for getting a select number of items from a group. It can be found from this question.

To get the latest 3 ids 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;

In short, the subquery in the where clause will filter for the largest id vlaues and you can limit it to less than or equal to 3. Notice that WHERE mt.dataid = m.dataid is what is being used to group the rows.

As the article suggests, this is not the most efficient way, but a clean way of writing it. A possibly more efficient way would be to use a UNION on each query individually. See the article for more information. It would look something like this:

(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)

Here is an SQL Fiddle for both example.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

  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>

Upvotes: 1

amazingacademy
amazingacademy

Reputation: 143

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

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

Upvotes: 0

0xGiddi
0xGiddi

Reputation: 554

In mysql this is done with the tow keywords: the first is DISTINCT that is used so: SELECT DICTINCT column FROM table this way only full unique rows are returned from the database.

the second keyword is used for limiting the number of records returned and is called LIMIT and is used so: SELECT x FROM y LIMIT number

in your case it will be something like SELECT DISTINCT * FROM table LIMIT 10

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

you may want to order the result by a specific column so that you get for example to 10 dates from the table.

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

Upvotes: 0

Related Questions