Display records in a particular order

Hi,
I want to display the result set of a query in a particular order.
My query looks like this -
SELECT col1,col2,col3,col4
FROM tab1
WHERE
(col2 = 1) AND (col3 = 1) AND (col4 = 5)
OR
(col2 = 1) AND (col3 = 1) AND (col4 = 3)
OR
(col2 = 1) AND (col3 = 1) AND (col4 = 1)
OR
(col2 = 2) AND (col3 = 1) AND (col4 = 4)
col1 is the primary key of tab1.
I want the result to be such that, all records with col4 value of 5
shud be displayed first, then those with col4 value of 3 , then col4 of
1 and then col4 of 4.
Problem is everytime i run the query, it gets sorted by col1
automatically.
Does anybody know how can I get the resultset in the desired order?Hi, snigs
Try something like this:
SELECT col1,col2,col3,col4 FROM tab1
WHERE col2=1 and col3=1 and col4 in (5,3,1,4)
ORDER BY CASE col4
WHEN 5 THEN 1
WHEN 3 THEN 2
WHEN 1 THEN 3
WHEN 4 THEN 4
END
or:
SELECT col1,col2,col3,col4,col5
FROM (
SELECT col1,col2,col3,col4,
CASE col4
WHEN 5 THEN 1
WHEN 3 THEN 2
WHEN 1 THEN 3
WHEN 4 THEN 4
END as col5
FROM tab1
WHERE col2=1 and col3=1 and col4 in (5,3,1,4)
) x
ORDER BY col5
Razvan|||...order by CASE col4 WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 1 THEN 3 ELSE 4 END
"snigs" <snigdhagulhati@.gmail.com> wrote in message
news:1133371494.533720.67620@.g44g2000cwa.googlegroups.com...
> Hi,
> I want to display the result set of a query in a particular order.
> My query looks like this -
> SELECT col1,col2,col3,col4
> FROM tab1
> WHERE
> (col2 = 1) AND (col3 = 1) AND (col4 = 5)
> OR
> (col2 = 1) AND (col3 = 1) AND (col4 = 3)
> OR
> (col2 = 1) AND (col3 = 1) AND (col4 = 1)
> OR
> (col2 = 2) AND (col3 = 1) AND (col4 = 4)
> col1 is the primary key of tab1.
> I want the result to be such that, all records with col4 value of 5
> shud be displayed first, then those with col4 value of 3 , then col4 of
> 1 and then col4 of 4.
> Problem is everytime i run the query, it gets sorted by col1
> automatically.
> Does anybody know how can I get the resultset in the desired order?
>|||That worked!!!
Thanks a lot for your help!!!!!!!
Raymond D'Anjou wrote:
> ...order by CASE col4 WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 1 THEN 3 ELSE 4 END
> "snigs" <snigdhagulhati@.gmail.com> wrote in message
> news:1133371494.533720.67620@.g44g2000cwa.googlegroups.com...|||"snigs" <snigdhagulhati@.gmail.com> wrote in message
news:1133463913.069320.267700@.g44g2000cwa.googlegroups.com...
> That worked!!!
> Thanks a lot for your help!!!!!!!
I like to get this kind of message back from the poster.
Not neccessary the "Thanks", although that's good too.
It's the "That worked!!!" that I like.
Glad to help you out.