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.
Author |
Topic |
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-09-14 : 23:14:36
|
this query:select strssn,zadid,strfirstname,strlastname,vartransactionid from vwcomplete order by strssngives me results like this:111111111 9999 Jaunice Green 3013321111111111 10000 Jaunice Green 3066871 222222222 10000 Julie Goodknecht 86632333333333 9999 Merlin Harris 3043126444444444 10000 Katherine Corriveau 429289444444444 10000 Katherine Corriveau 429373444444444 10000 Katherine Corriveau 471121what i need is to get only records that have 2 records with the same ssn and different zadid's. for example, in the results above, i would only want the first 2 records. 2 records where ssn is 111111111 and the zadid is different. how do i accomplish this? |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-14 : 23:26:48
|
hi mike - I think this is what you want - select distinct a.strssn, a.zadid, a.strfirstname, a.strlastname, a.vartransactionid from vwcomplete a, vwcomplete b where a.strssn = b.strssn and a.zadid <> b.zadidorder by a.strssn let me know if that's not it...(not sure what you wanted to do if there were more than 2 records etc)Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-15 : 00:18:49
|
is this what you're looking for?select * from vwcomplete wherestrsssn in (select strsssnfrom vwcompletegroup by strsssnhaving count(strsssn)=2) |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-15 : 00:20:51
|
jen, you're anwer doesn't take into account where the sssn is the same, but the zadid is not...reading between the lines, I suspect the requirement is to list all records with the same ssn but not the same zadid - not just two - but who knows?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-15 : 01:41:44
|
when i tested it using his data, it came out as expected, the group by groups the data into similar strssn and then counts the number of strssn in the group if equal to two then similar strssn by two.his example's expected result is two records...thanks for the feedback though...quote: Originally posted by rrb jen, you're anwer doesn't take into account where the sssn is the same, but the zadid is not...reading between the lines, I suspect the requirement is to list all records with the same ssn but not the same zadid - not just two - but who knows?--I hope that when I die someone will say of me "That guy sure owed me a lot of money"
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-15 : 01:43:52
|
Sorry -didn't mean it in a nasty way...I'll be keen to hear if he's going to clarifyquote: what i need is to get only records that have 2 records with the same ssn and different zadid's
--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-15 : 01:48:59
|
don't worry, it's taken as feedback... |
|
|
|
|
|
|
|