Distinct selection - 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

Transact-SQL (2005)

Distinct selection

Author

Topic

foamy
Starting Member

12 Posts

Posted - 2008-10-28 : 10:25:46

Hi guys,

Is there a way to specify that a specific column must be distinct in a query like this:

SELECT Column1, Column2, Column3 FROM Table WHERE Column1 IS DISTINCT

'IS DISTINCT' will obviously need to be changed into something else. How can I achieve this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:29:56

[code]SELECT Column1,MAX(Column2),MAX(Column3) FROM Table GROUP BY Column1[/code]

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-28 : 10:31:04

Well, you must decide which values for Column2 and Column3 should be returned. Something that will work and probably not return the expected result:

SELECT Column1, MAX(Column2), MAX(Column3)
...
GROUP BY Column1

foamy
Starting Member

12 Posts

Posted - 2008-10-28 : 10:34:23

That was quick :)

I need to select all rows for these columns, all I want is to make sure that Column1 is not returned twice with the same value. This makes me wonder about the MAX statements..?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 10:38:13

Remember that there is absolutely no guarantee that max(col2) and max(col3) derives from same record.
See ROW_NUMBER()

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:38:44

quote:
Originally posted by foamy

That was quick :)

I need to select all rows for these columns, all I want is to make sure that Column1 is not returned twice with the same value. This makes me wonder about the MAX statements..?

when you make column1 distinct, you can retrieve only one set of values for other columns along with it. what values are you interested in? recent,first or random?

foamy
Starting Member

12 Posts

Posted - 2008-10-28 : 10:40:09

I see what you mean... First, I guess :)

Which means using MIN() ...

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:44:32

then use this


SELECT Column1,Column2,Column3
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column2) AS Seq,Column1, Column2, Column3 FROM Table 
)t
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources