Avatar of Larry Brister
Larry BristerFlag for United States of America

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'
Microsoft SQL Server 2008Microsoft SQL Server.NET Programming

Avatar of undefined
Last Comment
Larry Brister
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Larry Brister
Larry Brister
Flag of United States of America image

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?
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
Avatar of Larry Brister
Larry Brister
Flag of United States of America image

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'
as I said: you need to double-check the data vs the results with the minimum test cases.
Avatar of Larry Brister
Larry Brister
Flag of United States of America image

ASKER

ok...going to do my homework and testing
Avatar of Larry Brister
Larry Brister
Flag of United States of America image

ASKER

After review...exactly what I needed.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo