CODEWITHSUNDEEP
sqlsql-server
stonypaul
stonypaul

Reputation: 695

SQL Top 1 query on multiple columns

I have a script which returns the following table. If I put the script in a subquery and give it a pseudonym, what script would generate the top row by EVENT_DATE for each CARE_ID? This has to be compatible with SQL2000. Thank you.

    CARE_ID EVENT_ID    EVENT_TYPE  EVENT_DATE
    3       18          B           13/07/2010 00:00
    78      11          C           27/07/2009 00:00
    78      9           T           28/07/2009 00:00
    151     49          T           21/03/2010 00:00
    217     102         C           30/03/2010 00:00
    355     111         C           16/07/2010 00:00
    355     56          T           17/07/2010 00:00
    364     774         C           23/08/2012 00:00
    369     117         C           28/07/2010 00:00
    631     74          T           15/01/2010 00:00
    631     148         C           02/02/2010 00:00
    1066    91          T           15/11/2010 00:00
    2123    280         T           10/07/2011 00:00
    2265    448         C           31/05/2011 00:00
    2512    183         B           04/02/2014 00:00
    2691    906         C           12/01/2014 00:00
    2694    307         T           15/06/2011 00:00
    2694    544         C           02/07/2011 00:00
    2892    85          B           19/12/2011 00:00
    2892    641         C           13/02/2012 00:00
    3038    660         C           09/08/2011 00:00
    3162    407         T           15/04/2012 00:00
    3178    780         C           01/09/2012 00:00
    3311    175         B           27/01/2014 00:00
    3344    869         C           01/10/2013 00:00
    3426    474         T           13/07/2013 00:00
    3606    479         T           03/01/2014 00:00
    3770    917         C           11/01/2014 00:00

Upvotes: 1

Views: 2210

Answers (2)

Sparky
Sparky

Reputation: 15115

Try this, assume the earliest date is top row

select x.care_id,min(x.event_date) as FirstDate
from <table> x
group by x.care_id

To get all information, you need a bit more

select x.care_id,a.event_id,a.event_type,x.firstDate as Event_date
from <table> a
join (select b.care_id,min(b.event_date) as FirstDate
      from <table> b
      group by b.care_id ) x  
on a.care_id=x.care_id and a.event_date=x.firstDate

Just type in on the fly, but should get you what you need.

Caveat, if care_id have identical event dates, you might get some duplicate rows.

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40576

This is somewhat inefficient, but I see no better way to do it in SQL Server 2000:

select
  t1.care_id,
  t1.event_id,
  t1.event_type,
  t1.event_date
from TheTable t1
join TheTable t2
  on t1.care_id = t2.care_id
  and t1.event_date >= t2.event_date
group by
  t1.care_id,
  t1.event_id,
  t1.event_type,
  t1.event_date
having count(*) = 1

The query currently returns the most recent record per care_id. If you need the oldest, just change the >= to <=.

SQLFiddle: http://www.sqlfiddle.com/#!3/98536/6

A potential issue with the query above is that if you have two records with the same (latest) event_date, it will return none. Let me know if such cases are possible in your data set.

Upvotes: 1

Related Questions