not completely exclusive select statement

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

not completely exclusive select statement

Author

Topic

amason
Starting Member

7 Posts

Posted - 2008-01-28 : 10:49:55

Hello,
I am trying to pull entire rows from a table that looks like this
these rows have another key btw
acn | type
1234 | A
1234 | A
1234 | B
1234 | B
9999 | B

I need to return from this two rows from this table, either one of the
acn 1234 type A rows,
and acn 9999 row

I have tried something like this in the where:
where
(type = 'A') OR
(type = 'B' and NOT EXISTS (Select * from table where type ='B'))

with this I get all of the type A rows, but none of the type B rows...

any help is greatly appreciated.
Andrew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 10:53:02

SELECT tmp.acn,tmp.type
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY acn ORDER BY type) AS RowNo,
acn,
type
)tmp
WHERE tmp.RowNo=1

amason
Starting Member

7 Posts

Posted - 2008-01-28 : 11:10:12

is tmp for a temporary table?? cause right now the data is in a temp table... should i put it into a real table to make this solution work??
thanks again!@

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:20:45

tmp is just the name for the derived table in the query. Visakh missed to write the FROM part.

SELECT yak.acn,yak.type
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY acn ORDER BY type) AS RowNo,
acn,
type
from yourtablenamehere
) as yak
WHERE yak.RowNo = 1

But with this little choice of columns, a simple

SELECT Acn, MIN(Type) AS Type
FROM Table1 GROUP BY Acn

E 12°55'05.25"
N 56°04'39.16"

amason
Starting Member

7 Posts

Posted - 2008-01-28 : 11:24:58

Problem solved!
Thanks Peso and Visakh!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources