ORDER BY clause [ how to sort by querystring] 1

[marthirial]

Here is the case, and sorry if I am asking something already answered but couldn't find what I need.

I have a table with three columns. by default by loading the page, this table will show the records sorted by date from newest to oldest.

ORDER BY sortvar DESC

where sortvar is a querystring for my field called date.

Now the issue I cannnot resolve is that if sortvar uses the querystring title, for example, the sort will be DESC again, meaning titles with first letter Z will be first and A will be last.

How do I write the ORDER BY so SQL knows that if sortvar is date_ it should go DESC but if it is title should go ASC.

Thanks and I will keep searching while I get any help.

 

[donutman]

Without seeing how sortvar is computed, it's difficult to give you a direct answer. However, conditional sorting can be done with the case statement:

[Blue]ORDER[/Blue] [Blue]BY[/Blue] [Blue]CASE[/Blue] SomeCond [Blue]THEN[/Blue] FieldTitle [Blue]ELSE[/Blue] [Gray]NULL[/Gray] [Blue]END[/Blue] ASc[Gray],[/Gray]
         [Blue]CASE[/Blue] SomeCond [Blue]THEN[/Blue] FieldDate [Blue]ELSE[/Blue] [Gray]NULL[/Gray] [Blue]END[/Blue] [Blue]DESC[/Blue]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[marthirial]

This is my SQL Statement

SELECT PK_ID, title, location, date_, format
FROM database.table
ORDER BY sortvar DESC

sortvar is a variable that will get its value from a Request.Querystring("sortvar") from three possible options in the URL
_date
format
title

 

[JamesLean]

It sounds to me like you are building this query from an ASP page. If so, you just need to put the logic in the ASP script, something like:

sSQL = "SELECT foo FROM bar "

sSort = Request.Querystring("sortvar")

If sSort = "a" Then
  sSQL = sSQL & "ORDER BY col1"
ElseIf sSort = "b" Then
  sSQL = sSQL & "ORDER BY col2 DESC"
ElseIf sSort = "c" Then
  sSQL = sSQL & "ORDER BY col3, col4 DESC"
End If

--James

 

[donutman]

If you want to use a SP that has a @SortVar parameter, then it would work like this:

[Blue]ORDER[/Blue] [Blue]BY[/Blue] [Blue]CASE[/Blue] @SortVar
            [Blue]WHEN[/Blue] [red]'format'[/red] [Blue]THEN[/Blue] format
            [Blue]WHEN[/Blue] [red]'title'[/red] [Blue]THEN[/Blue] title
            [Blue]ELSE[/Blue] [Gray]NULL[/Gray] [Blue]END[/Blue] ASC[Gray],[/Gray]            
         [Blue]CASE[/Blue] @SortVar[Gray]=[/Gray][red]'date'[/red]
            [Blue]THEN[/Blue] date_ [Blue]ELSE[/Blue] [Gray]NULL[/Gray] [Blue]END[/Blue] [Blue]DESC[/Blue]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]