Solved

Simple query!

Posted on 2014-11-18
14
113 Views
Last Modified: 2014-11-18
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
Comment
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
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 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

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
 
LVL 49

Expert Comment

by:Vitor Montalvão
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 65

Expert Comment

by:Jim Horn
ID: 40449799
Yes, copy-paste error on my part, corrected.  Thanks.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 400 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

Open in new window

0
 

Author Comment

by:soozh
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 51

Expert Comment

by:Huseyin KAHRAMAN
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

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
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

Open in new window

0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
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 65

Expert Comment

by:Jim Horn
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

by:soozh
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 65

Expert Comment

by:Jim Horn
ID: 40449919
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
 

Author Comment

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

thanks
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
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 65

Expert Comment

by:Jim Horn
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 49
Formating field in mysql Advance formatting 1 39
Substring works but need to tweak it 14 31
How come this XML node is not read? 3 28
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question