Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY - special edition.....
ORDER BY - special edition..... [message #187622] Mon, 14 August 2006 13:27 Go to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Consider the following:

SELECT * FROM emp ORDER BY deptno DESC


EMPNO                  ENAME      JOB       MGR                    HIREDATE                  SAL                    COMM                   DEPTNO                 
---------------------- ---------- --------- ---------------------- ------------------------- ---------------------- ---------------------- ---------------------- 
7698                   BLAKE      MANAGER   7839                   01-MAY-81                 2850                                          30                     
7844                   TURNER     SALESMAN  7698                   08-SEP-81                 1500                   0                      30                     
7499                   ALLEN      SALESMAN  7698                   20-FEB-81                 1600                   300                    30                     
7654                   MARTIN     SALESMAN  7698                   28-SEP-81                 1250                   1400                   30                     
7521                   WARD       SALESMAN  7698                   22-FEB-81                 1250                   500                    30                     
7900                   JAMES      CLERK     7698                   03-DEC-81                 950                                           30                     
7788                   SCOTT      ANALYST   7566                   19-APR-87                 3000                                          20                     
7566                   JONES      MANAGER   7839                   02-APR-81                 2975                                          20                     
7369                   SMITH      CLERK     7902                   17-DEC-80                 800                                           20                     
7876                   ADAMS      CLERK     7788                   23-MAY-87                 1100                                          20                     
7902                   FORD       ANALYST   7566                   03-DEC-81                 3000                                          20                     
7839                   KING       PRESIDENT                        17-NOV-81                 5000                                          10                     
7934                   MILLER     CLERK     7782                   23-JAN-82                 1300                                          10                     
7782                   CLARK      MANAGER   7839                   09-JUN-81                 2450                                          10


How would you adjust the sql so that employees of deptno 20 are listed first and then the other departments ASC.

I thought creating an extra column with the result of an sort of an if statement in sql followed by an ORDER BY newcolomn,deptno would do the trick, thing is....
I can't figure out the functions needed.
And coming up with a solution of functions that doesn't exists is well..... kinda easy....

[Updated on: Mon, 14 August 2006 13:36]

Report message to a moderator

Re: ORDER BY - special edition..... [message #187630 is a reply to message #187622] Mon, 14 August 2006 14:09 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Try this:
SELECT * FROM emp
order by decode(deptno,'20',1,2),deptno desc;
Re: ORDER BY - special edition..... [message #187650 is a reply to message #187622] Mon, 14 August 2006 16:04 Go to previous messageGo to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Ok from here I grasp:
http://openacs.org/forums/message-view?message_id=60485

Quote:

Oracle's handy decode function works as follows:
decode(expr, search, expr[, search, expr...] [, default])


But how would you solve it using CASE since it also works on postgres?

Well here is my solution which works on oracle, but will it work on postgress?

Quote:

select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
case when DEPTNO = 20 then '0'
else '1'
end as item
from emp ORDER BY ITEM,DEPTNO ASC;


[Updated on: Mon, 14 August 2006 16:05]

Report message to a moderator

Re: ORDER BY - special edition..... [message #187712 is a reply to message #187650] Tue, 15 August 2006 02:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can replace DECODE with CASE in @joy_divisions example like this:

SELECT * FROM emp
order by case when deptno = '20' then 1
              else 2
              end
        ,deptno desc;
Re: ORDER BY - special edition..... [message #187732 is a reply to message #187650] Tue, 15 August 2006 04:32 Go to previous message
Duke
Messages: 14
Registered: August 2006
Junior Member
Quote:

Well here is my solution which works on oracle, but will it work on postgress?


The short answer: Yes.

Thanks everybody for their input,
love this forum already!
Previous Topic: Urgent:Please help me out in Date Conversion
Next Topic: Export data to an excel file
Goto Forum:
  


Current Time: Sat Dec 10 01:12:56 CST 2016

Total time taken to generate the page: 0.10880 seconds