1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Order by in oracle with priority

Discussion in 'SQL PL/SQL' started by meo_spt, Nov 23, 2012.

  1. meo_spt

    meo_spt Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a table (emp) with three columns in ORACLE:

    COMPANY OFFICE EMP
    ------------------------
    9999 00001 emp1
    9999 00001 emp2
    9999 00002 emp3
    9999 00002 emp4
    0001 99999 emp5
    0001 00001 emp6
    0001 00002 emp7
    0002 00001 emp8
    0002 99999 emp9
    0003 99999 emp10
    0003 00001 emp11
    0003 00002 emp12


    I want to get value in EMP column with following statement:

    --------------
    SELECT
    EMP
    FROM
    emp
    ORDER BY COMPANY (But 9999 is first), OFFICE (But 99999 is first).
    -------------


    I don't know how to do. Please help me do this. Thank in advance! :)
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Code (Text):

    SELECT EMP
    FROM emp
    ORDER BY COMPANY DESC, OFFICE DESC;
     
    Regards,
    Dariyoosh
     
  3. meo_spt

    meo_spt Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Oh, I mean ORDER BY COMPANY ASC, OFFICE ASC but 99999 in COMPANY and OFFICE is always first. I found the solution:

    SELECT EMP
    FROM emp
    ORDER BY
    case when company = 9999 then 0 else 1 end,
    company,
    case when office = 99999 then 0 else 1 end,
    office

    Thanks;
     
  4. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Are you sure that this fulfils your requirement? Here is what I get when I run your suggested solution

    Code (Text):

    WITH tmptab AS
    (
        SELECT '9999' AS company, '00001' AS office, 'emp1' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00001' AS office, 'emp2' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00002' AS office, 'emp3' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00002' AS office, 'emp4' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '99999' AS office, 'emp5' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '00001' AS office, 'emp6' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '00002' AS office, 'emp7' AS emp FROM DUAL UNION
        SELECT '0002' AS company, '00001' AS office, 'emp8' AS emp FROM DUAL UNION
        SELECT '0002' AS company, '99999' AS office, 'emp9' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '99999' AS office, 'emp10' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '00001' AS office, 'emp11' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '00002' AS office, 'emp12' AS emp FROM DUAL
    )
    SELECT company, office, emp
    FROM tmptab
    ORDER BY
    case when company = 9999 then 0 else 1 end,
    company,
    case when office = 99999 then 0 else 1 end,
    office;



    COMP OFFIC EMP
    ---- ----- -----
    9999 00001 emp1
    9999 00001 emp2
    9999 00002 emp4
    9999 00002 emp3
    0001 99999 emp5
    0001 00001 emp6
    0001 00002 emp7
    0002 99999 emp9
    0002 00001 emp8
    0003 99999 emp10
    0003 00001 emp11
    0003 00002 emp12

    12 rows selected.

    SQL>
     

    First regarding to what ? rows in your table are not ordered.


    Regards,
    Dariyoosh
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Apparently you are storing numbers in character fields; possibly this would be the proper solution:

    select emp
    from
    (select emp, to_number(Company) cmpny, to_number(office) offce
    from emp) e
    order by cmpny, offce;
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    OP wants the "99999" in COMPANY and OFFICE is always first through columns are ordered in ascending order.

    Though the expected pattern is not clear, the following query may give OP a kick start.

    Code (SQL):
    WITH tmptab AS
    (
        SELECT '9999' AS company, '00001' AS office, 'emp1' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00001' AS office, 'emp2' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00002' AS office, 'emp3' AS emp FROM DUAL UNION
        SELECT '9999' AS company, '00002' AS office, 'emp4' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '99999' AS office, 'emp5' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '00001' AS office, 'emp6' AS emp FROM DUAL UNION
        SELECT '0001' AS company, '00002' AS office, 'emp7' AS emp FROM DUAL UNION
        SELECT '0002' AS company, '00001' AS office, 'emp8' AS emp FROM DUAL UNION
        SELECT '0002' AS company, '99999' AS office, 'emp9' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '99999' AS office, 'emp10' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '00001' AS office, 'emp11' AS emp FROM DUAL UNION
        SELECT '0003' AS company, '00002' AS office, 'emp12' AS emp FROM DUAL
    )
    SELECT company, office, emp
    FROM tmptab
    ORDER BY
    CASE WHEN company = 99999 THEN 0 ELSE CASE WHEN office = 9999 THEN 0 ELSE 1 END END,
    CASE WHEN company = 9999 THEN 0 ELSE 1 END,
    CASE WHEN office = 99999 THEN 0 ELSE 1 END,
    company,
    office;