Help with sorting criteria

[Guest_imported]

Hello SQL Gurus,

I have a table with a field call C_TYPE which can have values within
range "E", "M", "A", "N". I also have a date field C_DATE.

I want a Select SQL which will be give me records sorted by Date and within that by C_TYPE. Now the problem is that
the sorting by C_TYPE has to be in order of E M A & N only.

Can somebody help me out with this situation.

Thanx

 

[rac2]

There are probably lots of ways to do this but here is an obvious method.

First make a table that will hold the codes and the sort order you want for them -

CREATE TABLE type_sort_order(
   c_type CHAR(1), //use same datatype as your main table
   c_type_order INT
)

Fill it with data like this -

c_type c_type_order
E 1
M 2
A 3
N 4

Then query like this -

SELECT a.*
   FROM my_table a
   JOIN TABLE type_sort_order b ON b.c_type = a.c_type
   ORDER BY a.c_date, b.c_type_order

Voila!

 

[tlbroadbent]

If your RDMS has the CASE statement you can to the following.

Select *
From Table
Order By
C_Date,
Case C_Type
When 'E' Then 1
When 'M' Then 2
When 'A' Then 3
When 'N' Then 4
End Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[angiole]

I have used the following to specifiy my own sort order.
You'll appreciate that this can be impractical for a large number of group items, but if you only have 4, try this (the decode statement is Oracle version of if then else).

select decode(type,'E',' ','M',' ','') || type as type_ordered,
date
from the_table
group by decode(type,'E',' ','M',' ','') || type

What we're doing here is prefixing the E and M type with a space so that the sorter will natrually move it up in the order.