select distinct depending on 1 field

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 2008 Forums

Transact-SQL (2008)

select distinct depending on 1 field

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-03-31 : 03:38:34

I want to make a query without doubles concerning this condition

i want all rows without the doubles they find on field "remoteid" is null.

so when u have following rows in the db

id remoteid name name2
1 0100 AAA AAA
2 0202 BBBB BBBB
3 NULL BBBB BBBB

i only want rows 1 and 2, but when row 2 isnt there i want values 1 and 3

*EDIT*

remoteid is a varchar ! so max wont work... could you take the row with the longest length for example ?

amaze me guru's :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 08:15:25

Here is a way, but I am not quite happy with it because I am forced to use distinct. But it should work at least for your sample code.

select distinct
   coalesce(b.id,a.id) as Id,
   coalesce(b.remoteId,a.remoteId) as remoteId,
   a.name,
   a.name2
from
   YourTable a
   outer apply
   (
      select * 
      from YourTable b 
      where remoteId is not null 
         and a.name = b.name 
         and a.name2 = b.name2
   ) b

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 08:28:03

DECLARE @Table Table (id int, remoteid varchar(4), name1 varchar(4),name2 varchar(4))
INSERT INTO @Table
SELECT 1,'0100','AAA','AAA' UNION ALL
SELECT 2,'0202','BBBB','BBBB' UNION ALL
SELECT 3,NULL,'BBBB','BBBB'

select *
from
(
select id,remoteid,name1,name2
,[rown] = row_number() over(partition by name1,name2 order by name1)
from @table
) a

where rown = 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources