|
Subject:
|
Select Distinct Order by...
|
|
Posted By:
|
MichaelTJ
|
Post Date:
|
11/11/2003 2:05:40 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
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
11/11/2003 2:13:22 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:
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
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
11/16/2003 11:49:45 PM
|
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
|
