Distinct with Order by Problem

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 with Order by Problem

Author

Topic

Baatezu
Starting Member

1 Post

Posted - 2007-11-12 : 20:16:40

I have a table that I am trying to pull data from a single field, sorted by the primary key and not have any duplicate data come up.
The table consists of just the PriKey and Data fields.
I only need the value from the Data field and no duplicate values, but if the PriKey data is there as well, not a problem.
I've tried many variations on selecting grouping and ordering, the main problem I am encountering is the error
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
I'm at a loss... It seems something like this would be kinda easy...
Any help would be much appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 20:25:09

subquery?

Select DISTINCT Data
FROM (Select Distinct primarykey,data
FROM [Table]
order by primarykey)

Poor planning on your part does not constitute an emergency on my part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-13 : 01:15:13

Make sure to change out the name column names "PriKey", and "Data" with your actual column names, along with changing the table name "Mytable" to your table name.


 Select a.Prikey,a.Data
From
(
select	Row_Number() Over(Partition by aa.Data order by a.PriKey) as RowID,aa.PriKey,aa.Data
From 
mytable aa
) a
where 
a.RowID = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources