1

I have a table that resembles the below:

Id   datetime      reference   key
1    16/1/16 9:00  123456      21
2    16/1/16 9:00  123456      22
3    16/1/16 9:00  123456      23
4    16/1/16 9:00  123456      24
5    16/1/16 9:20  123456      21
6    16/1/16 9:20  123456      22
7    16/1/16 9:20  123456      23
8    16/1/16 9:20  123456      24
9    16/1/16 9:40  123456      21
10   16/1/16 9:40  123456      22
11   16/1/16 9:40  123456      23
12   16/1/16 9:40  123456      24

What I am trying to do is extract the first datetime of a specific key eg 22 and then the datetime of the next occurance of key 22. So in the example above I would only want id's 2 and 6. The key could appear 3,4,5,6 or any number of times for a single record but I only want the first two times it appears. The above example only uses one record signified by reference 123456 but there could be thousands of records and I want the first two times of all records.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Gavin
  • 31
  • 4
  • [Duplicate](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) of the common [tag:greatest-n-per-group] problem, with the slightly-less-common difference of `n` being more than 1. – Clockwork-Muse Jan 17 '16 at 14:25

1 Answers1

2

Use windowed functions:

SELECT *
FROM (
  SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY [reference], [key] ORDER BY [datetime])
  FROM your_table) AS sub
WHERE rn <= 2;

LiveDemo

If you want to handle ties ([reference], [key], [datetime] is not unique) use DENSE_RANK instead.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275