DISTINCT - 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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

DISTINCT

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-26 : 12:24:05

Is there a way to DISTINCT rows without being resorted automatically?

Meaning, if I have the following:

Col1
=======
Adam777
Andrew
Adam777
Jane
Susan

So I need output:

Col1
=======
Adam777
Andrew
Jane
Susan

Thanks. Sounds simple.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 12:26:46

[code]SELECT Col1
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS Seq,Col1
FROM Table)t
WHERE Seq=1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 12:30:42

[code]
SELECT IDENTITY(int,1,1) AS ID,
Col1
INTO #Temp
FROM Table

SELECT t.Col1
FROM #Temp t
OUTER APPLY (SELECT COUNT(*) AS RecCnt
FROM #Temp
WHERE ID<t.ID
AND Col1=t.Col1)tmp
WHERE ISNULL(tmp.RecCnt,0) =0
[/code]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-26 : 12:36:34

Once again, thanks!

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-26 : 12:51:16

Sorry, I keep forgetting I'm using SQL 2000. If it's not too much trouble please provide 2K equivalent. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 04:32:29

quote:
Originally posted by rgombina

Sorry, I keep forgetting I'm using SQL 2000. If it's not too much trouble please provide 2K equivalent. Thanks.

SELECT IDENTITY(int,1,1) AS ID,
Col1
INTO #Temp
FROM Table

SELECT t.Col1,
(            SELECT COUNT(*) 
             FROM #Temp
             WHERE ID<t.ID
             AND Col1=t.Col1
)AS RecCnt 
FROM #Temp t
WHERE ISNULL(RecCnt,0) =0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 04:52:34

What is wrong with this?

select distinct data from
(
select 'Adam777' as data union all
select 'Andrew' union all
select 'Adam777' union all
select 'Jane' union all
select 'Susan'
) t

Madhivanan

Failing to plan is Planning to fail

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-27 : 11:08:35

Sorry bad example but here's a better scenario:

Col1
=======
Zack
Adam777
Andrew
Adam777
Jane
Susan

So output with DISTINCT:

Col1
=======
Adam777
Andrew
Jane
Susan
Zack

I really need this:

Col1
=======
Zack
Adam777
Andrew
Adam777
Jane
Susan

So I need output:

Col1
=======
Zack
Adam777
Andrew
Jane
Susan

So Zack remains the first in the order after removing duplicates.

Thanks and I'm going to try visakh's statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources