Select Distinct Order by...

MichaelTJ · November 11th, 2003, 03:05 PM

Hi,
I've tried to order a querry, but it keeps giving me a bug..
Here goes:

*********************CODE************************* ********
Select Distinct right(left(ryCreatedDate,4),2) FROM RYKTE where right(left(ryCreatedDate,6),2) = '03' ORDER BY ryCreatedDate ASC
************************************************** *********

I'm getting this error (From querry analyzer)
*********************Error************************ *********
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
************************************************** *********

I just can't figure it out.. Anybody seen this before?

------------------------
All help is Good help!
Regards
Michael

Jeff Mason · November 11th, 2003, 03:13 PM

The error message says it all. You have an expression in the SELECT list, not the table's column - give the expression a column alias:

Code:

SELECT DISTINCT right(left(ryCreatedDate,4),2) as TheYear
    FROM RYKTE
    WHERE right(left(ryCreatedDate,6),2) = '03'
ORDER BY TheYear

(P.S. Are you storing this date as a string?)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

MichaelTJ · November 17th, 2003, 12:49 AM

Sorry I could not respond sooner, my internet link has been down for a while.
Yes I'm storing it at string. I've unfortunently taken over a base with a couple 100K entries. And my predessesor left me with this..?..

I found the fault, but as my link has benn down for quite awhile I haven't been able to post(damn those ISP's, ALWAYS fu...ing up!)
Here is what I did..
*****************WORKING***************
Select Distinct right(left(ryCreatedDate,4),2) FROM RYKTE where right(left(ryCreatedDate,6),2) = '03' ORDER BY right(left(ryCreatedDate,6),2) ASC
***************************************

Thanx for your reply, nice to know there is help out there when stuck..

------------------------
All help is Good help!
Regards
Michael