March 31, 2006 at 11:44 am
Hi,
I need to sort records by a project number. The project number is varcahr data type and consisted of a last two digit of year and a project number making a uique id for a project. The records range from 1999 to a present time. The projects from a year 1999 have their numbers begin with '99-xxxxx'; year 2000 with '00-xxxxx'; 2001 with '01-xxxxx', and so on.
I need to show the users project information from recent one to older ones, so I used order by vchProjectNum desc. This put 1999 projects on top of the list. Is there any way to fix this issue?
Thanks.
Dong
March 31, 2006 at 11:55 am
ORDER BY
CASE WHEN LEFT(projectnum,2) = '99' THEN 19 ELSE 20 END,
projectnum
March 31, 2006 at 12:06 pm
Eight years ago, answering this question spawned an entire industry.
You could put something together with a derived table, by adding a column to your original query, wrapping at all up in another SELECT, and sorting on the new column (There are other ways of performing this same trick, such as performing the testing in the where clause, etc. This is just one possible solution.)
-- The new outer query
SELECT column1, column2, column3, ...
FROM ( -- the original query with the added column
SELECT column1, column2, column3, ...and the rest of your original query,
-- plus one more column to sort with
CASE WHEN CONVERT(tinyint, LEFT([ProjectID], 2)) > 50
THEN '19'
ELSE '20'
END + ProjectID AS [Sorter]
FROM ...the rest of your original query
) a
-- Then sort on the new column
ORDER BY Sorter-Eddie
Eddie Wuerch
MCM: SQL
March 31, 2006 at 12:59 pm
I used Kevin's method because I ran your code to get an error message 'conversion error'. I found a project number 'DM05-012-02'. I have no idea how it got in here. I'll ask accountants what this is.
Thank you guys for your help.
Dong
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply