Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Simple query!

Posted on 2014-11-18
Medium Priority
117 Views
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
Question by:soozh
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4
• 3
• +1

LVL 66

Assisted Solution

Jim Horn earned 400 total points
ID: 40449781
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
``````
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

LVL 52

Expert Comment

ID: 40449789
Jim, just one correction. Should be MIN function and not MAX since he wants the first visit and not the last one.
0

LVL 66

Expert Comment

ID: 40449799
Yes, copy-paste error on my part, corrected.  Thanks.
0

LVL 59

Accepted Solution

HainKurt earned 1600 total points
ID: 40449802
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
``````
0

Author Comment

ID: 40449805
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

LVL 59

Expert Comment

ID: 40449813
or this

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

LVL 66

Expert Comment

ID: 40449827
>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
``````
0

LVL 59

Expert Comment

ID: 40449847
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

LVL 66

Expert Comment

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

Author Closing Comment

ID: 40449895
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

LVL 66

Expert Comment

ID: 40449919
0

Author Comment

ID: 40449971
your articule is pretty good esp. the more complicated functions such as over, partition, rank.

thanks
0

LVL 59

Expert Comment

ID: 40450044
>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

LVL 66

Expert Comment

ID: 40450152
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month8 days, 22 hours left to enroll