CODEWITHSUNDEEP
sql
droidsites
droidsites

Reputation: 1035

ordering the query output based on the data division

SELECT col2,col3 FROM (
     select * from (
               select * from A 
                 MINUS 
               select * from A where col3 LIKE '63%'
           ) ORDER BY CASE WHEN col3 like '63%' THEN 2 ELSE 1 END
         )

This gives the output rows as,

      col1  col2   col3
       ----  ----   ----

 1.     x      b     123
 2.     a      y     247
 3.     n      m     634
 4.     l      o     639

Basically the output is divided into two parts as the part that shows col3 NOT like '63%' (Rows 1 and 2) and col3 like '63%' (Rows 3 and 4)

Now I need to sort each part by col1. i.e. Sort rows 1 and 2 and rows 3 and 4. So the output should be, 

        col1  col2   col3
       ----  ----   ----

 1.     a      y       247
 2.     x      b     123
 3.     l      o     639
 4.     n      m     634

someone help me in what should added to the query please.

Edit: Well I am not sure and clear about this idea but does the group by is help ful in this scenario

Thanks

Upvotes: 0

Views: 67

Answers (2)

Zsolt Botykai
Zsolt Botykai

Reputation: 51603

Well I'm not sure if I

SELECT col2,col3 
  FROM ( select * 
           from ( select * from A 
                  MINUS 
                  select * from A where col1 LIKE '63%'
                ) 
          ORDER BY CASE WHEN col1 like '63%' THEN 2 ELSE 1 END
        )

Can give you this result: 

     col1  col2   col3
     ----  ----   ----

1.     x      b     123
2.     a      y     247
3.     n      m     634
4.     l      o     639

As in the original query the output is only two columns (select col2,col3 from).

And your query can be simplified a bit, like

SELECT col1, col2, col3 
  FROM A 
 WHERE col1 NOT LIKE '63%'                    
 ORDER BY CASE WHEN col3 like '63%' THEN 2 ELSE 1 END

Now you can append another rule to ORDER BY:

SELECT col1, col2, col3 
  FROM A 
 WHERE col1 NOT LIKE '63%'                    
 ORDER BY CASE WHEN col3 like '63%' THEN 2 ELSE 1 END, col1

HTH

Upvotes: 0

Alexander Malakhov
Alexander Malakhov

Reputation: 3529

You can order by any set of expressions. Separate them by ','

SELECT col2,col3 
  FROM a
 ORDER BY (CASE WHEN col1 like '63%' THEN 2 ELSE 1 END), col1

Upvotes: 1

Related Questions