1

I'm trying to build a personalized query for my table which consists on obtaining the latest N records for each person. My table schema is as follows:

+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| person    | varchar(100)        | NO   | PRI | NULL    |       |
| time      | bigint(20) unsigned | NO   | PRI | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+

For example, if I had the following data:

+---------+-------+
| person  | time  |
+---------+-------+
| A       | 2     |
| A       | 7     |
| B       | 1     |
| B       | 6     |
| B       | 4     |
| C       | 3     |
+---------+-------+

and my N = 2, the expected result would be:

+---------+-------+
| person  | time  |
+---------+-------+
| A       | 7     |
| A       | 2     |
| B       | 6     |
| B       | 4     |
| C       | 3     |
+---------+-------+

(Just last 2 results for each person, order by person asc, time desc)

I know I can get all the person names first and select/order/limit them one by one, but I'm curious to know if I could to this in one query, and let the DBMS do the hard work. For N = 1, I successfully used max and group by statements:

SELECT person, max(time) as time FROM table GROUP BY person ORDER BY person ASC, time DESC

But I don't know how to extend it for greater values of N. Can you please help me?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
André
  • 323
  • 4
  • 18
  • looks like duplicate of existing http://stackoverflow.com/questions/17991943/sqlite-how-to-select-first-n-row-of-each-group – Sumit Tyagi May 29 '14 at 18:57
  • What if the 2nd and 3rd record for 1 user both have the same `time`? Which one to show? – Rudie May 29 '14 at 18:57
  • Sounds like http://stackoverflow.com/q/21257396/247372 – Rudie May 29 '14 at 18:59
  • @Rudie forgot to mention that the pair (person, time) is a primary key, not allowing duplication. So it's not possible to have, for example (Z, 2), (Z, 2), and all numbers are integers. – André May 29 '14 at 19:06
  • For those who pointed as duplicate, I think you are right, I didn't know the name of this problem, but the questions look very similar to this one. I will try to find a suitable query for my problem. – André May 29 '14 at 19:07

1 Answers1

0

Ok, I found the solution using the @Rudie link:

select * from (
    select a.* from points as a
    left join points as a2
    on a.person= a2.person and a.time <= a2.time
    group by person, time
    having count(*) <= 2) a
order by person asc, time desc;

Live example: http://sqlfiddle.com/#!2/58026/2

It's working very well. Thank you all for the contributions!

André
  • 323
  • 4
  • 18