Unique select statement

Larry Brister
Larry Brister used Ask the Experts™
on
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'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:
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

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
this article describes the issue and solutions in general: http://www.experts-exchange.com/A_3203.html


select afsSource 
  from yourtable 
group by afsSource
having count(*) = 1
  and max(afsSourceDetail) <> 'ALL'

Open in new window

Larry Bristersr. Developer

Author

Commented:
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?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 Bristersr. Developer

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
ok...going to do my homework and testing
Larry Bristersr. Developer

Author

Commented:
After review...exactly what I needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial