dynamic order by in stored proc with "desc"
-
I have a case statement in a stored proc, that does a dynamic sort of some of the columns in my database based on which case statement is selected.
They all are sorted "asc" except one, which is a smalldatetime. I cant concatenate the table name and the "desc" , I think because its not a string?
I tried to cast to a varchar but then the dates dont sort properly.
***Here is a sample of my code that works but sorts the date by oldest first.
ORDER BY
CASE @OrdSeq WHEN 1 THEN customername + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 2 THEN customerpo + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 3 THEN status + "asc" END,
CASE @OrdSeq WHEN 4 THEN fe + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 5 THEN cbccontact + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 6 THEN ipccontact + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 7 THEN agilentrecdate + "asc" ELSE NULL END
END
GO
** This sorts by something incorrect ??? I want newest date first
ORDER BY
CASE @OrdSeq WHEN 1 THEN customername + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 2 THEN customerpo + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 3 THEN status + "asc" END,
CASE @OrdSeq WHEN 4 THEN fe + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 5 THEN cbccontact + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 6 THEN ipccontact + "asc" ELSE NULL END,
CASE @OrdSeq WHEN 7 THEN cast(agilentrecdate as varchar(20)) + " desc" ELSE NULL END
END
GO
Thanks,
Dave
-
This might help :
ORDER BY
CASE @OrdSeq WHEN 1 THEN customername END,
CASE @OrdSeq WHEN 2 THEN customerpo END,
CASE @OrdSeq WHEN 3 THEN status END,
CASE @OrdSeq WHEN 4 THEN fe END,
CASE @OrdSeq WHEN 5 THEN cbccontact END,
CASE @OrdSeq WHEN 6 THEN ipccontact END,
-- descending dates
CASE @OrdSeq WHEN 7 THEN datediff(mi,agilentrecdate ,getdate()) END,
-- ascending dates
CASE @OrdSeq WHEN 8 THEN datediff(mi,getdate(),agilentrecdate) END
not the best solution I feel......
-
thanks winash, I will give it a try, what does the mi do ?
-
"mi" indicates the difference between current date and the value of the date field in minutes (which is used to order the dates in ascending or descending order)...
you could also use "dd" (for number of days), "hh" (for number of hours) , "ss" (for number of seconds)...
-
Hi, thanks again, this worked, dont know why,
Thankyou
Dave
ORDER BY
CASE @OrdSeq WHEN 1 THEN customername ELSE NULL END asc,
CASE @OrdSeq WHEN 2 THEN customerpo ELSE NULL END asc,
CASE @OrdSeq WHEN 3 THEN status ELSE NULL END asc,
CASE @OrdSeq WHEN 4 THEN fe ELSE NULL END asc,
CASE @OrdSeq WHEN 5 THEN cbccontact ELSE NULL END asc,
CASE @OrdSeq WHEN 6 THEN ipccontact ELSE NULL END asc,
CASE @OrdSeq WHEN 7 THEN agilentrecdate ELSE NULL END desc
-
ahhh...now thats a simple solution...
this works since you have put the sort order out of the CASE statement...previously adding the "asc" or "desc" required that the whole CASE thingy be converted to varchar...
and thanks for this bit of code...
-
Well , here is where I posted the original question, and you can see the answer , I must give credit and thanks, to Simon Sabin for the answer.
Dave
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply