i want current CTC of each employee following is the design of my table
Ecode Implemented Date Salary
7654323 2010-05-20 350000
7654322 2010-05-17 250000
7654321 2003-04-01 350000
7654321 2004-04-01 450000
7654321 2005-04-01 750000
7654321 2007-04-01 650000
i want oracle query for following output
ECODE Salary
7654321 650000
7654322 250000
7654323 350000
f you want to keep the last salary for each
ecode sorted by implemented_date:SQL> WITH data AS (
2 SELECT 7654323 Ecode, '2010-05-20' Implemented_Date, 350000 Salary
3 FROM DUAL
4 UNION ALL SELECT 7654322, '2010-05-17', 250000 FROM DUAL
5 UNION ALL SELECT 7654321, '2003-04-01', 350000 FROM DUAL
6 UNION ALL SELECT 7654321, '2004-04-01', 450000 FROM DUAL
7 UNION ALL SELECT 7654321, '2005-04-01', 750000 FROM DUAL
8 UNION ALL SELECT 7654321, '2007-04-01', 650000 FROM DUAL
9 )
10 SELECT ecode,
11 MAX(salary)
12 KEEP (dense_rank FIRST ORDER BY Implemented_Date DESC) sal
13 FROM DATA
14 GROUP BY ecode;
ECODE SAL---------- ----------
7654321 650000
7654322 250000
7654323 350000