Removing Duplicates from a select query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Removing Duplicates from a select query?

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 12:16:29

Hi Again!

I have a simple select query which is returning 10 rows:

select * from TABLE1

Which returns the following:

Col1 Col2 Col3
----- ------------ ------
Item1 Description1 100
Item2 Description2 99
Item3 Description3 98
Item4 Description4 97
Item1 Description1 96
Item1 Description1 95
Item2 Description2 94
Item2 Description2 93
Item5 Description5 92
Item1 Description1 91
Item1 Description1 90

What I want to do is return the distinct items in Col1 but in that order - ordered by Col3.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:18:30

What do you want to do with Col3 values? do you waqnt their min or max value for each Col1?

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 12:20:54

Max Value really!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:22:51

Ok here it is:-

SELECT t.Col1,t.Col2,t.Col3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col3 DESC) AS RowNo,
Col1,
Col2,
Col3
FROM
TABLE1
)t
WHERE t.RowNo=1

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 14:04:28

Or


Select Col1, Col2, Max(Col3) As Col3
From Table1
Group By Col3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:26:55

quote:
Originally posted by Qualis

Or

Select Col1, Col2, Max(Col3) As Col3
From Table1
Group By Col3

That should be

Select Col1, Col2, Max(Col3) As Col3
From Table1
Group By Col1, Col2

Madhivanan

Failing to plan is Planning to fail

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-11 : 03:19:12

Fantastic - Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources