Find the smallest value from multiple records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find the smallest value from multiple records

Author

Topic

3rookerbar
Starting Member

8 Posts

Posted - 2007-06-14 : 14:44:58

I have a table with the following variables:

patid cddate nadcd4

Patients (patid) have multiple records. I am trying to find the lowest nadcd4 for each patient along with the associated cddate. I can do a min(nadcd4) and find the lowest nadcd4 by patid but I can't seem to get the associated cddate. Any suggestions? Thanks

wal

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 14:48:34

Check the FAQ at the top of this page which has a link for what you are asking. You want a MIN for each group.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

3rookerbar
Starting Member

8 Posts

Posted - 2007-06-14 : 15:06:48

Dinakar,

Thanks for the info but I am having trouble finding the link. I tried doing a min on each group but I guess I have the syntax wrong. This code works but I don't get cddate.

SELECT cdcount.PATID, Min(cdcount.NadCD4) AS NaDCD4
FROM cdcount
GROUP BY cdcount.PATID;

If I add cddate to the select statement then it doesn't work. I know why it doesn't work because min is an aggregate function. How do you get around this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 15:15:31

This might help: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

3rookerbar
Starting Member

8 Posts

Posted - 2007-06-14 : 15:24:46

Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-14 : 16:12:30

It is simply a two-step process. you already know step 1, which is this:


SELECT cdcount.PATID, Min(cdcount.NadCD4) AS NaDCD4
FROM cdcount
GROUP BY cdcount.PATID;

All you have to do now is select from your original table and join to the results of the above. The JOIN will restrict the rows returned so that only rows with the MIN(NaDCD4) per PATID are returned:


select 
  cdcount.*
from
  cdcount
inner join
  (above sql here) x on cdcount.PATID = x.PATID and cdcount.NaDCD4 = x.NaDCD4

Always break your SQL down into smaller pieces and then you can put them together via JOINS to get the results you need. I hope this helps.

By the way -- those are horrible table and column names!!!

- Jeff
http://weblogs.sqlteam.com/JeffS

3rookerbar
Starting Member

8 Posts

Posted - 2007-06-15 : 10:34:33

Thanks a million Jeff,

I knew how to do this in SAS but didn't have access to the program for this task. I knew there was more to it. I will brush up on joins. Thanks again.

Wal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources