[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Simple query!

i have a table called measurements which has a column pat_id and visitdate.

The pat_id column identifies the patient, and the visitdate is the date of the patients visit.  A patient can have many visits.

How do i find the first visit for every patient?
0
soozh
Asked:
soozh
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That would be a simple GROUP BY on artist, and MIN date.
SELECT pat_id , Min(visitdate) as earliest_visit_date
FROM measurements 
GROUP BY pat_id

Open in new window

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jim, just one correction. Should be MIN function and not MAX since he wants the first visit and not the last one.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, copy-paste error on my part, corrected.  Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
Here it is:

SELECT * from (
SELECT pat_id , visitdate, row_number() over (partition by pat_id order by visitdate desc) rn
FROM measurements 
)x where rn=1

Open in new window

0
 
soozhAuthor Commented:
What i need is the whole row.

When i think about i could have easily done the Group by.  Its getting the row that i dont know how todo.

However each row has an id column so i could use the Group by to identify the rows and then use a second select to get the rows.
0
 
HainKurtSr. System AnalystCommented:
or this

SELECT * from (
  SELECT m.*, row_number() over (partition by pat_id order by visitdate) rn
  FROM measurements m
) x where rn=1

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What i need is the whole row.
It would help if you could state all requirements in the original question, so experts don't spin their wheels giving a solution that doesn't meet all requirements.

The row number / partition / order by solution should work.

Also, give this a whirl, which determines the min date for each pat_id in a subquery, then joins on the whole table based on pat_id and visitdate to minimum visitdate.
SELECT m.*
FROM measurements m
   JOIN (SELECT pat_id , Min(visitdate) as earliest_visit_date
         FROM measurements 
         GROUP BY pat_id) m_min_dt ON m.pat_id = m_min_dt.pat_id AND m.visitdate = m_min_dt.earliest_visit_date

Open in new window

0
 
HainKurtSr. System AnalystCommented:
Row_Number()

http://msdn.microsoft.com/en-us/library/ms186734.aspx

group by/min/max join with original table does not look a good idea :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>group by/min/max join with original table does not look a good idea :)
Please explain why.  Works on my end.
0
 
soozhAuthor Commented:
Sorry Jim.  i wrote "How do i find the first visit for every patient? "  I should have written how do i get the row.  However what i did not write was i wanted the visit date.  I guess i was not 100% clear.

So i used HainKurst solution in a cte and did a join on it....

Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sounds like a plan.  Thanks for the split, good luck with your project, and if the article helps you please click the big green 'Was this article helpful?' at the end.  -Jim
0
 
soozhAuthor Commented:
your articule is pretty good esp. the more complicated functions such as over, partition, rank.

thanks
0
 
HainKurtSr. System AnalystCommented:
>group by/min/max join with original table does not look a good idea :)
Please explain why.  Works on my end.

of course it works... just it is old way of doing this before those nice functions

Ranking Functions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189798.aspx

and I guess it is much faster & light on resources on server... also, the solution may give multiple records if there are records with same min/max value... grouping whole table and joining again with table does not seems best option especially when there are new ways of writing such queries...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks.  I have an update in the works with a lot more scenarios, but I'm being bombarded by certain individuals for me to write an SSIS article or three, as there are very few here.  I'll send you a message once I've published the update.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now