Multiple Order By in one Select 2

[JeanPhil]

Ok I have a simple SELECT * FROM table

The table looks like :

Code | date | credit
5546 | 01/01/2004 | C
6875 | 12/25/2003 | C
9378 | 12/30/2003 | C
2254 | 12/30/2003 | D
9675 | 01/15/2004 | D
8574 | 12/15/2003 | D


What I need is to sort my record differently depending on a value of the credit field ! Ex: If the field credit is C I need to sort the records by code and if the credit field is D I need to sort by date. I don't know if it's even possible but any suggestion would be greatly appreciated !!

 

[r937]

order by credit
, case when credit='C' then
else [date] end

rudy

http://r937.com/

SQL Consulting

 

[Golom]

Here's a MS/Access approach. You need another field that you can ORDER BY

   Select Code, [DateField], Credit,
          (Credit & IIF ( Credit = "C", 
                          Format ([DateField], "yyyymmdd"),
                          Code )) As SortField
   From tbl

   ORDER BY 4

You will need to replace the IIF with a SELECT CASE construct if you are using SQL Server, Oracle, etc.

 

[JeanPhil]

Thanks to both of you !!

I still have a little problem with the Format function ... I use SQL Server as Golom mentionned so I changed the query to use the SELECT CASE but I still need to convert the datefield to a string !! Any Idea ?

Here's an example of my query :

SELECT * FROM temp_concil ORDER BY credit, case when credit='C' then number else [date] end;

It's the [date] part that create the error !!

 

[JeanPhil]

Thanks again ... I found what I needed for the conversion part !!