asked on

Unique select statement

I have a table with 3 columns

afsid afsSource afsSourceDetail

afsid is UID (int)
afsSource is a varchar(12) column and can be duplicated

afsSourceDetail is a status code column

What I need is to find all cases in my table where afsSource exists once and afsSourceDetail <> 'ALL'

Ephraim Wangoya

try

select * 
from
(
	select afsid, afsSource, afsSourceDetail, row_number() over(partition by afsSource order by afsSource) RN
	from Table1
	where afsSourceDetail <> 'ALL'
) A
where A.RN = 1

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Hey...you guys were at the same time...maybe a split second apart.

Any problems splitting the points since they both work?

Even though I like angelll's better?

Guy Hengel [angelIII / a3]

actually, the 2 statements will NOT produce the same output.

mine will only return if there is exactly 1 record, which is <> 'ALL'

ewangoya's will return if, ignoring all records <> 'ALL', there is only 1 left.

so, you might need to double-check what you need
CHeers

Larry Brister

ASKER

angelIII:

I need ANY instance where a afsSOurce occurs once and the detail <> 'ALL'

So...there may be 25000 instances of afsSource that each occur once in the table...and 12000 of them the afsSourceDetail <> 'ALL'

Guy Hengel [angelIII / a3]

as I said: you need to double-check the data vs the results with the minimum test cases.

Larry Brister

ASKER

ok...going to do my homework and testing

Larry Brister

ASKER

After review...exactly what I needed.