By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Selecting a record for each unique ID

P: 2
Hi everyone,

I'm having a problem writing out the SQL statement and unfortunately, I can't even produce you a working draft version simply because I cannot wrap my head around how to start it. I think its best if I show how the data looks first and then the question.

PersonID EncounterID Value
16099 E3434 23
16099 E3434 24
16099 A1232 5
13567 E1533 1
13567 E1560 15
23432 A3245 2

Ok, so the question is.. how do i write the query such that the result only shows for each personID, the lowest value so the result I am looking for is

PersonID EncounterID Value
16099 A1232 5
13567 E1533 1
23432 A3245 2

Ultimately, it is the EncounterID I am after but for each unique PersonID there is.

If anyone can help out, that would be great! I've search the site for some ideas but I'm not even sure of what to look for, so if this has been discussed, please let me know so that I don't waste anyone's time.

Thank you!
Nov 27 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,615
Try this (you need to modify it to fill in the correct info that wasn't provided) :
Expand|Select|Wrap|Line Numbers
  1. SELECT PersonID,
  2.   Mid(Min(Format([Value],'00000') & [EncounterID]),6) AS MinEncounter, 
  3.   Min([Value]) AS MinVal
  4. FROM YourTable
  5. GROUP BY PersonID
Nov 27 '06 #2

P: 2
That's exactly what I am looking for! Thank you so much. Now to check help for what "Mid" does. Thanks again :D


Try this (you need to modify it to fill in the correct info that wasn't provided) :
Expand|Select|Wrap|Line Numbers
  1. SELECT PersonID,
  2.   Mid(Min(Format([Value],'00000') & [EncounterID]),6) AS MinEncounter, 
  3.   Min([Value]) AS MinVal
  4. FROM YourTable
  5. GROUP BY PersonID
Nov 27 '06 #3

NeoPa
Expert Mod 15k+
P: 31,615
That's exactly what I am looking for! Thank you so much. Now to check help for what "Mid" does. Thanks again :D
I so like that attitude.
Often it's "How do I use Mid?" or "What do I have to do with my fingers to make the code come up on my computer?".
So I can be a little prone to exaggeration (or hyperbole for the purists).
Nov 27 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.