| Author |
Topic |
|
sqlslick
Yak Posting Veteran
83 Posts |
 Posted - 2012-10-18 : 14:18:41
|
| Hey guys,I need to extract the first appointment for each patient from a sample recordset (sorted by PATIENT_NAME, APPT_DATE) similar to the one below:PATIENT_NAME | APPT_DATE | APPT_CODEPATIENT A | 2012-07-02 08:00 | APATIENT A | 2012-07-11 10:00 | APATIENT A | 2012-07-13 11:00 | APATIENT B | 2012-07-05 08:00 | APATIENT B | 2012-07-09 11:00 | AHow do I accomplish this? If I use TOP 1 it will only give me one record for the entire record set.Much appreciated,John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 14:21:52
|
| [code]SELECT PATIENT_NAM,APPT_DATE,APPT_CODEFROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY PATIENT_NAME ORDER BY APPT_DATE) AS SeqFROM PatientTable)tWHERE Seq=1orSELECT pt.*FROM PatientTable ptINNER JOIN (SELECT PATIENT_NAME,MIN(APPT_DATE) AS FirstApptDate FROM PatientTable GROUP BY PATIENT_NAME)pt1ON pt1.PATIENT_NAME = pt.PATIENT_NAME AND pt1.FirstApptDate = pt.APPT_DATE [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2012-10-18 : 14:54:24
|
| Great, thanks!But, I just used the good old MIN() function on the APPT_DATE column and it worked like a charm.Thanks any ways for your prompt reply!John |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 14:56:41
|
| thats my second suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2012-10-18 : 16:15:51
|
| Actually I got a little too far ahead of myself. The MIN() function is not working because I have to GROUP BY PATIENT_NAME and APPT_CODE and by doing that it gives me the first appointment for each APPT_CODE by PATIENT_NAME. I forgot to mention that there are multiple APPT_CODE values (i.e. A, B, C, etc.). How can I still grab the very first one APPT_DATE (sort APPT_DATE ASC) for each patient?Thanks in advanced!J |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 16:21:31
|
quote:
Originally posted by sqlslick
Actually I got a little too far ahead of myself. The MIN() function is not working because I have to GROUP BY PATIENT_NAME and APPT_CODE and by doing that it gives me the first appointment for each APPT_CODE by PATIENT_NAME. I forgot to mention that there are multiple APPT_CODE values (i.e. A, B, C, etc.). How can I still grab the very first one APPT_DATE (sort APPT_DATE ASC) for each patient?Thanks in advanced!J
see my second suggestiongroup on patient_name alone and take min of appt_date. create a derived table as shown and then join to it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2012-10-19 : 10:11:03
|
| Hello,I actually got your first suggestion to work. I had additional WHERE clause conditions in the outter SELECT but should have been in the inner SELECT. I just needed to recharge the brain and re-assess.Thanks so much for your help. Learned something new ... AGAIN!J |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-19 : 22:36:27
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
First Record for Each Patient