select Qry

Question

Post reply

select Qry

dirge86

SSC Rookie

Points: 25
More actions
August 19, 2010 at 3:36 pm

#387619

Need help to write a select Qry pull only 1 CustID from an applicationID that has multiple custids
for example:
custid appID lastname firstname
1 1 john doe
2 1 jane doe
3 2 joe doe
I only need appid 1 with one name, not both.
please help!
the qry would pull only:
1 1 john doe
3 2 joe doe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why not

1 1 jane doe

3 2 joe doe

What's the rule to select one name out of many? Min(id), Min(name), max(name) or something else?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dirge86 SSC Rookie Points: 25 More actions · Answer 2

let me try to be more specific:

table name - heldapps

column names:

studentnumber, applicationid, lastname, firstname

several studentid's can belong to one applicationID. I need the qry to only pull 1 name and 1 app. even if there are 3 kids belonging to one app, i only need one of those kids and the appid. i'm stumped at the moment. right now, i have a total of 8000 studentnumbers belonging to 8000 applicationd's. the qry i need will narrow the applicationid's down to around 4000.

Teemo Ten Centuries Points: 1376 More actions · Answer 3

As Lutz said, please specify the rules, because it need to know what will be the basis of getting the names. base on your example on getting 1 name only in 3 student name with the same appID, what is the condition on selecting that 1 name on the 3? or you just have to select randomly on that 3 names just to achieve the output?