Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
display old and new values for n values of a column in a table

Hi All,
I have a table . I will update the table with the new value . I need to display old and new values as column. I tried PIVOT but since my entries are n in number i can't give definite values in FOR IN () in PIVOT .
EG:
CREATE TABLE TAB_A (ID NUMBER, EMP_ID NUMBER,NAME VARCHAR2(10),SALARY NUMBER);
INSERT INTO TAB_A (1,123,'Mike',1000);
INSERT INTO TAB_A (2,124,'Pete',2000);
INSERT INTO TAB_A (3,125,'Kevin',3000);
INSERT INTO TAB_A (4,123,'Mike',3000);
INSERT INTO TAB_A (5,124,'Pete',4000);
I need to display NAME,EMP_ID,OLD_SALARY,NEW_SALARY .
In real time , SALARY and EMP_ID are in definite not concluded to 3 values as mentioned above so I am not able to use FOR IN () . Please kind your advise
Regards,
Uday
Best Answer
-
SELECT NAME, EMP_ID, SALARY OLD_SALARY, LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY FROM TAB_A ORDER BY EMP_ID, ID / NAME EMP_ID OLD_SALARY NEW_SALARY ---------- ---------- ---------- ---------- Mike 123 1000 3000 Mike 123 3000 Pete 124 2000 4000 Pete 124 4000 Kevin 125 3000 SQL>
SY.
Answers
-
Hi, @Uday_N
Thanks for posting the sample data. Don't forget to post the exact results you want from the given data and an explanation of how you get those results from that data (e.g., how do you know if two rows refer to the same person, and which one is newer than the other).
-
Hi, @Uday_N
Would you like to get answers that work? Make sure the CREATE TABLE and INSERT statements you post work, too. Test (and, if necessary, fix) your statements before you post them.
-
SELECT NAME, EMP_ID, SALARY OLD_SALARY, LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY FROM TAB_A ORDER BY EMP_ID, ID / NAME EMP_ID OLD_SALARY NEW_SALARY ---------- ---------- ---------- ---------- Mike 123 1000 3000 Mike 123 3000 Pete 124 2000 4000 Pete 124 4000 Kevin 125 3000 SQL>
SY.
-
Hi Frank,
I apologize for the error caused .
My desired o/p is :
emp_id name old_salary new_salary
123 Mike 1000 3000
124 Pete 2000 4000
Here in real time emp_id and salary may not be fixed to a few values .
Sorry once again .
Regards,
Uday
-
Hi Solomon,
@Solomon Yakobson Thanks for your answer. It worked . Thanks once again .
@Frank Kulash - I aplogize for my mistake . Sorry for it.
Regards,
Uday
-
How about the scenario where an employee salary is updated more than once like the ones highlighted in bold below?
How would like to have your output in this case?
INSERT INTO TAB_A (1,123,'Mike',1000);
INSERT INTO TAB_A (2,124,'Pete',2000);
INSERT INTO TAB_A (3,125,'Kevin',3000);
INSERT INTO TAB_A (4,123,'Mike',3000);
INSERT INTO TAB_A (5,124,'Pete',4000);
INSERT INTO TAB_A (6,123,'Mike',5000);
Regards,
Sreekanth
-
What difference does it make?
SELECT NAME, EMP_ID, SALARY OLD_SALARY, LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY FROM TAB_A ORDER BY EMP_ID, ID / NAME EMP_ID OLD_SALARY NEW_SALARY ---------- ---------- ---------- ---------- Mike 123 1000 3000 Mike 123 3000 5000 Mike 123 5000 Pete 124 2000 4000 Pete 124 4000 Kevin 125 3000 6 rows selected. SQL>
As you can see, Mike's salary was 1000 and was bumped up to 3000. Then his 3000 salary was bumped up to 5000. Next time his salary changes you will see one more row with old salary 500 and nes salary what ever it will be.
SY.
-
Hi SY,
The query will correctly return all the changes as you mentioned.
But I was trying to find out what exactly the OP wants in this case - a) Should we display all the changes made to an employee's salary or b) should we display the latest change that was made.
I did not want to assume and hence asked the question.
Regards,
Sreekanth