CODEWITHSUNDEEP
sqlfield
Alessandro Di Maggio
Alessandro Di Maggio

Reputation: 147

SQL query to retrieve only one occurrence for each id

This is my (simplified) table:

id     eventName       seriesKey    eventStart
1      Event1          5000         2012-01-01 14:00:00
2      Event2          5001         2012-01-01 14:30:00
3      Event3          5000         2012-01-01 14:50:00
4      Event4          5002         2012-01-01 14:55:00
5      Event5          5001         2012-01-01 15:00:00
6      Event6          5002         2012-01-01 15:30:00
7      Event7          5002         2012-01-01 16:00:00

I have to build a query that orders the table by eventStart (ASC) but for each seriesKey, I need only one occurrence.

Thank you very much

Upvotes: 2

Views: 17635

Answers (3)

Stephen Quan
Stephen Quan

Reputation: 26011

Try aggregating with GROUP BY and using aggregate functions like MIN().

SELECT seriesKey,
       MIN(eventStart) eventStart
FROM   events
GROUP  BY seriesKey;

This results in:

5000    2012-01-01 14:00:00.000
5001    2012-01-01 14:30:00.000
5002    2012-01-01 14:30:00.000

If your're interested in all columns from events table, not just the above two columns I choose, there's a freaky implementation in some databases (e.g. SQL Server) which may help you:

SELECT *
FROM   events e1
WHERE  e1.ID IN
(
       SELECT   TOP 1 e2.ID
       FROM     events e2
       WHERE    e2.seriesKey = e1.seriesKey
       ORDER BY e2.eventStart
);

Resulting in:

1   Event1  5000    2012-01-01 14:00:00.000
2   Event2  5001    2012-01-01 14:30:00.000
6   Event2  5002    2012-01-01 14:30:00.000

Upvotes: 4

user330315
user330315

Reputation:

If you also need the other columns associated with the key, you have two options:

select *
from (
  select id, 
         eventName, 
         seriesKey,
         eventStart,
         row_number() over (partition by seriesKey order by eventStart) as rn
  from the_event_table
) t
where rn = 1
order by eventStart

or for older DBMS that do not support windowing functions:

  select t1.id, 
         t1.eventName, 
         t1.seriesKey,
         t1.eventStart
  from the_event_table t1
  where t1.eventStart = (select min(t2.eventStart)
                         from the_event_table t2
                         where t2.seriesKey = t1.seriesKey)
  order by eventStart

Upvotes: 2

triclosan
triclosan

Reputation: 5724

you can get earlier date for each seriesKey:

select * from 
(
select seriesKey, min(eventStart) as mindate
group by seriesKey
)
order by mindate

Upvotes: 0

Related Questions