DISTINCT question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

DISTINCT question

Author

Topic

billroberts
Starting Member

14 Posts

Posted - 2009-06-23 : 13:18:52

Hello !

Assuming I have:
SELECT distinct
[CustomerID]
FROM
[Northwind].[dbo].[Orders]

If in my results I find 'ALFKI', I only want 'ALFKI' returned... If not, I want everything else returned.

How can I achieve this ?

thx for reading !

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:22:22

[code]
if exists (select * from [Northwind].[dbo].[Orders] where [CustomerID] = 'ALFKI')
begin
select 'ALFKI'
end
else
begin
SELECT distinct
[CustomerID]
FROM
[Northwind].[dbo].[Orders]
end
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

billroberts
Starting Member

14 Posts

Posted - 2009-06-23 : 13:27:49

Eeek!

Is there's no INLINE way of making it work?

(btw, thx for replying and providing a reasonable solution!)

billroberts
Starting Member

14 Posts

Posted - 2009-06-23 : 13:33:53

I oversimplified my question but real world, i have a query which hits multiple tables and therefore feel the solution best applied with in the 'where' clause.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:34:50

Then with given parameter.
SELECT distinct
[CustomerID]
FROM
[Northwind].[dbo].[Orders]
where ([CustomerID] = @para1 or @para1 is null)

Given @para1 is 'ALFKI' -> resultset is 'ALFKI'
Given @para1 is NULL value --> resultset is everything else.

Edit: that was too fast - it is not ok - wait

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:39:07

Sorry I'm blocked.
Maybe later.
Sorry

No, you're never too old to Yak'n'Roll if you're too young to die.

billroberts
Starting Member

14 Posts

Posted - 2009-06-23 : 13:41:49

quote:
Originally posted by webfred

Sorry I'm blocked.
Maybe later.
Sorry

Not at all! Your solution prompted me to:
SELECT distinct
[CustomerID]
FROM
[Northwind].[dbo].[Orders]
where [CustomerID] = 'ALFKI'
or
not exists
(SELECT [CustomerID] FROM [Northwind].[dbo].[Orders] where [CustomerID] = 'ALFKI')

Unless you can find a hole, it looks sound!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:46:08

Looks cool

It was a hard day