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!

How to swap rows from the results of a select query?!

Discussion in 'SQL PL/SQL' started by Vicky, Jun 18, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Select * from employees;

    1 10 arun 5000 02-AUG-98
    2 20 bala 6000 01-JAN-91
    3 30 celin 7000 02-FEB-99
    4 40 dev 8000 30-MAR-92
    5 50 elisa 9000 23-JAN-99
    6 10 Neymer 10000 21-JAN-96
    7 7 broad 11000 21-MAR-96
    8 8 McCullam50000 21-MAR-96

    From the abov result I want to swap the rows 3 and 2.
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    use where condition
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I'm sorry, I can't get you.,. Could you help me with the query.,?!
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    show me desc employees..

    You didn't mention column names
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Here it is.,.,

    desc employees

    Name Null Type
    ------------- ---- ------------
    SNO NUMBER
    EMP_ID NUMBER
    DEPARTMENT_ID VARCHAR2(50)
    SALARY NUMBER
    DOB DATE
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Select * from employees;

    1 10 arun 5000 02-AUG-98
    2 20 bala 6000 01-JAN-91
    3 30 celin 7000 02-FEB-99
    4 40 dev 8000 30-MAR-92
    5 50 elisa 9000 23-JAN-99
    6 10 Neymer 10000 21-JAN-96
    7 7 broad 11000 21-MAR-96
    8 8 McCullam50000 21-MAR-96

    From the abov result I want to swap the rows 3 and 2.

    So for that use below query.

    Select * from employees where SNO not in (2,3);

    1 10 arun 5000 02-AUG-98
    4 40 dev 8000 30-MAR-92
    5 50 elisa 9000 23-JAN-99
    6 10 Neymer 10000 21-JAN-96
    7 7 broad 11000 21-MAR-96
    8 8 McCullam50000 21-MAR-96
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    what means to swap rows?
    if it is necessary to change row order, then it is necessary to use a necessary sorting option or analitic function of row_number()...


    Code (SQL):


    WITH your_data AS
    (SELECT 10 dept_no, 'arun' emp_name ,5000  salary, to_date('02-AUG-98','dd-mon-rr') dd FROM dual UNION ALL
    SELECT  20, 'bala', 6000 ,to_date ('01-JAN-91','dd-mon-rr') FROM dual UNION ALL
    SELECT  30 ,'celin' ,7000 ,to_date('02-FEB-99','dd-mon-rr') FROM dual UNION ALL
    SELECT  40 ,'dev' ,8000 ,to_date('30-MAR-92','dd-mon-rr') FROM dual
    )
    SELECT * FROM your_data
    ORDER BY decode(emp_name,'celin',6000,'bala',7000,salary);

    SQL>
       DEPT_NO EMP_NAME SALARY DD
    ---------- -------- ---------- -----------
            10 arun           5000 02.08.1998
            30 celin          7000 02.02.1999
            20 bala           6000 01.01.1991
            40 dev            8000 30.03.1992

     
     
    Vicky likes this.
  8. jagadekara

    jagadekara Forum Guru

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

    I am sorry! I though swap means restrict.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    No problem Reddy., Sergey's query works perfectly.,
     
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    Thanks Sergey., The query below works perfectly,.,

    select * from employees order by decode (emp_name,'bala',7000,'celin',6000,salary);
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    I wonder in which situation such a "hard-coded" DECODE statement which depends on the name of employee would be relevant or useful practically.

    I suppose this is just an exercise or for self-learning...

    In "real world" situations, we have to ORDER BY one of the employee table columns or a combination of columns (or expression), for ex., ORDER BY emp_id, salary.. etc...
     
  12. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Rajen,

    Yes, Rajen. You're so correct. Is there any possibilities to swap 2 rows without hard coding it.?!
     
  13. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    No, if you mean if there's any simple function or instruction in Oracle to do it.
    You have to sort of "design" an expression with a minimum of hard-coding and a combination of DECODE and/or ORDER BY.
    This is usually driven by your requirements and your data-model should accommodate such needs (a specific column in your table which would be your sort criteria).

    However, you can still achieve this swapping in a PL/SQL script: SELECT & FETCH all the records into a Table and display in the sequence you like.
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,All.

    Yes, my simple example is given here only for the training purpose to show that in a sentence of sorting it is possible to use for example - expressions.
    Naturally, the hrad-code was used for evident demonstration by data given in an example.


    Vicky it is necessary to define simply expression defining rules of sorting and to implement them in the form of expressions.

    If the requirement of "swap rows" can't be implemented by determination of sorting of data, then it is necessary to use collection pl/sql