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!

Customizing order of a query result..

Discussion in 'SQL PL/SQL' started by Vicky, Oct 8, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    how to customize the order of a query result..

    Code (SQL):
    SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_ID
    FROM hr.employees WHERE department_id='100' ORDER BY employee_id DESC;

    EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID
    ----------- -------------------- ---------- -------------
    113 Luis 6900 100
    112 Jose Manuel 7800 100
    111 Ismael 7700 100
    110 John 8200 100
    109 Daniel 9000 100
    108 Nancy 12000 100



    Here., I want employee_id '111' to displayed first, followed by others records of no specified order.,

    expected output:

    EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID
    ----------- -------------------- ---------- -------------
    111 Ismael 7700 100
    113 Luis 6900 100
    112 Jose Manuel 7800 100
    110 John 8200 100
    109 Daniel 9000 100
    108 Nancy 12000 100
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Why you need like that?, on what basis?
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Since, employee_id '111' is the manager_id here, i require '111' record to be listed first followed by all other records of no specific order/,.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    So,

    use manager_id in order by
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    At least in my copy of the sample schemas -- Nancy is the manager of that department. The following logic will sort as you requested (i.e. manager at the top -- but it's not employee 111). The DECODE/SUBSTR logic is a little kludgey. If I were to need something like this for a production use, I'd probably make a custom function that was more robust.

    Code (Text):

    SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_ID, JOB_ID
    FROM hr.employees
    WHERE department_id='100'
    ORDER BY DECODE(SUBSTR(job_id, -3), 'MGR', 1, 2);

    EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID JOB_ID
    ----------- -------------------- ---------- ------------- ----------
            108 Nancy                     12008           100 FI_MGR  
            113 Luis                       6900           100 FI_ACCOUNT
            111 Ismael                     7700           100 FI_ACCOUNT
            112 Jose Manuel                7800           100 FI_ACCOUNT
            109 Daniel                     9000           100 FI_ACCOUNT
            110 John                       8200           100 FI_ACCOUNT
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And yet connect by would work as well and not need DECODE:

    Code (SQL):
    SQL> SELECT employee_id, first_name, salary, department_id, job_id
      2  FROM employees
      3  WHERE department_id = 100
      4  CONNECT BY prior employee_id = manager_id
      5  START WITH employee_id = 108;

    EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID JOB_ID
    ----------- -------------------- ---------- ------------- ----------
            108 Nancy                     12000           100 FI_MGR
            109 Daniel                     9000           100 FI_ACCOUNT
            110 John                       8200           100 FI_ACCOUNT
            111 Ismael                     7700           100 FI_ACCOUNT
            112 Jose Manuel                7800           100 FI_ACCOUNT
            113 Luis                       6900           100 FI_ACCOUNT

    6 ROWS selected.

    SQL>
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Yep. Much less kludgey. CONNECT BY didn't occur to me because the hierarchy is so limited.