OraFAQ Forum: SQL & PL/SQL » ORDER BY

Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY - special edition.....

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORDER BY - special edition..... [message #187622]

Mon, 14 August 2006 13:27

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

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Try this:

SELECT * FROM emp
order by decode(deptno,'20',1,2),deptno desc;

Report message to a moderator

Re: ORDER BY - special edition..... [message #187650 is a reply to message #187622]

Mon, 14 August 2006 16:04

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

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;

Report message to a moderator

Re: ORDER BY - special edition..... [message #187732 is a reply to message #187650]

Tue, 15 August 2006 04:32

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!

Report message to a moderator

Previous Topic:	Urgent:Please help me out in Date Conversion
Next Topic:	Export data to an excel file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 20:14:27 CST 2024