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.

This post can help you: [http://stackoverflow.com/questions/10491474/mysql-select-distinct-multiple-columns-based-on-the-uniqueness-of-one-row][1] [1]: http://stackoverflow.com/questions/10491474/mysql-select-distinct-multiple-columns-based-on-the-uniqueness-of-one-row — Semih Eker, Oct 31 '14 at 14:34
@Jonny I wont post any technical questions here without even trying — prix, Oct 31 '14 at 14:38
What is your N in this case? **EDIT** I suppose it would be 3, wouldn't it? — AdamMc331, Oct 31 '14 at 15:12
@prix I'm also a little confused - in your results, dataid 1 is ordered by date descending, and dataid 2 isn't ordered at all. Can you double check your expected results? — AdamMc331, Oct 31 '14 at 15:21
@McAdam331 I edited my question and I removed the date, since I can get the latest record using max(id). But the thing is, I want to limit the result per dataId. — prix, Oct 31 '14 at 15:41
@Prix understood, my answer still applies just had to be tweaked, please see the edit. — AdamMc331, Oct 31 '14 at 15:48

score 1 · Answer 1 · answered Oct 31 '14 at 15:43

  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>

score 0 · Answer 2 · answered Oct 31 '14 at 14:36

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.

score 0 · Answer 3 · answered Oct 31 '14 at 14:36

0

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

answered Oct 31 '14 at 14:36

amazingacademy

143
1
9

Im trying to select distinct 'dataId' getting the N records based on date where N is a limit of records per unique 'dataId' – prix Oct 31 '14 at 14:44
2

This is not correct SQL syntax. `distinct` is not a function. – Gordon Linoff Oct 31 '14 at 14:45
DISTINCT syntax selects just the records that are different. @prix i'm trying to understand what you mean by N, null? if so what column contains the null fields you are searching. – amazingacademy Oct 31 '14 at 15:39
@amazingacademy N is the limit of rows per unique dataId. So, if N=3 my expected result would be 3 latest record with dataId=1 and 3 latest record with dataId=2 – prix Oct 31 '14 at 15:46
Sure but DISTINCT is *still* not a function. Those parentheses do nothing for you – Strawberry Oct 31 '14 at 15:46
at what point did I say it was? it does however return the desired output as mentioned above. – amazingacademy Oct 31 '14 at 17:44

score 0 · Accepted Answer · edited May 23 '17 at 11:49

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.

Select unique records limit with N rows

4 Answers4

Linked