error while using distinct top with Order By

I am trying to run a simple query
select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
ORDER BY x_date desc
I get this error-
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specifie
d.
Can anyone help?select DISTINCT TOP 10 email, code, x_date
from user_info where code like 'xx12%'
ORDER BY x_date desc
AMB
"Mike" wrote:

> I am trying to run a simple query
> select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
> ORDER BY x_date desc
> I get this error-
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is specif
ied.
> Can anyone help?
>|||Some backgrouung info from celko:
http://groups.google.de/groups?hl=d...r />
2540tk2ms
ftngp13.phx.gbl%26rnum%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike" <Mike@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FFEDAF14-77CE-46C1-AA15-363D99E265C6@.microsoft.com...
>I am trying to run a simple query
> select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
> ORDER BY x_date desc
> I get this error-
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> Can anyone help?
>|||The error message seems clear. You need to put x_Date in the Select statemen
t
like so:
Select Distinct TOP 10 x_Date, Email, Code
From User_Info
Where Code Like 'xx12%'
Order By x_Date Desc
Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FFEDAF14-77CE-46C1-AA15-363D99E265C6@.microsoft.com...
>I am trying to run a simple query
> select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
> ORDER BY x_date desc
> I get this error-
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is specif
ied.
> Can anyone help?
>|||you should put x_date in the select if it is used in the order by clause
SELECT DISTINCT TOP 10 email, code, x_date
FROM user_info
WHERE code LIKE 'xx12%'
ORDER BY x_date DESC
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FFEDAF14-77CE-46C1-AA15-363D99E265C6@.microsoft.com...
>I am trying to run a simple query
> select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
> ORDER BY x_date desc
> I get this error-
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> Can anyone help?
>|||You can put x_date in the SELECT list, as suggested by other posters, but
what I think you are after is:
select TOP 10 email,code
from user_info
where code like 'xx12%'
GROUP BY email,code
ORDER BY MIN(x_date) desc
Jacco Schalkwijk
SQL Server MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FFEDAF14-77CE-46C1-AA15-363D99E265C6@.microsoft.com...
>I am trying to run a simple query
> select DISTINCT TOP 10 email,code from user_info where code like 'xx12%'
> ORDER BY x_date desc
> I get this error-
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> Can anyone help?
>