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 |
|
ssunny
Posting Yak Master
133 Posts |
 Posted - 2012-08-13 : 16:28:39
|
| Hello Guys,I am sure this must be a simple one but for somereason I can't seem to get it.Here's my table structuretable : testaid aname aflag1 ny N2 nj N3 pa Y3 pa N4 ct N4 ct N4 ct YResult I want:aid aname aflag1 ny N2 nj N3 pa Y4 ct YLogic:So if there is multiple records for a given aid like aflag = 'Y' and aflag = 'N' , I want to select the one where aflag = 'Y'Cheers. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-13 : 16:44:03
|
| [code]select aid,aname,aflagfrom (select row_number() over (partition by aid,aname order by case when afalg='Y' then 1 else 2 end) AS Seq,*from test)twhere Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-08-13 : 17:03:30
|
| Works great Visakh. Thank you for always helping out people like me. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-13 : 17:09:42
|
no problem. you're welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-08-13 : 18:31:43
|
| Just an alternative; not sure which might be more performant, if either.[CODE]select sid, aname, MAX(aflag) aflagfrom testgroup by sid, aname[/CODE]=================================================Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961) |
|
|
|
|
|
|
Query Help