Home » SQL & PL/SQL » SQL & PL/SQL » Need to get the top 3 employees whose salary increased in three consecutive year (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Product ,window)
| Need to get the top 3 employees whose salary increased in three consecutive year [message #662374] |
Thu, 27 April 2017 07:12  |
 |
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
|
Junior Member |
|
|
Rule:
1) need to get the top 3 employee whose salary increased in 3 consecutive year.
with data as
(select 1 empid, 500 sal, 2001 year
from dual
union all
select 1 empid, 400 sal, 2002 year
from dual
union all
select 1 empid, 600 sal, 2003 year
from dual
union all
select 2 empid, 500 sal, 2001 year
from dual
union all
select 2 empid, 700 sal, 2002 year
from dual
union all
select 2 empid, 800 sal, 2003 year
from dual
union all
select 2 empid, 900 sal, 2004 year
from dual
union all
select 3 empid, 800 sal, 2001 year
from dual
union all
select 3 empid, 500 sal, 2002 year
from dual
union all
select 3 empid, 200 sal, 2003 year
from dual
union all
select 3 empid, 700 sal, 2004 year
from dual
union all
select 3 empid, 500 sal, 2005 year
from dual
union all
select 4 empid, 800 sal, 2001 year
from dual
union all
select 4 empid, 900 sal, 2002 year
from dual
union all
select 4 empid, 1000 sal, 2003 year
from dual
union all
select 4 empid, 2000 sal, 2004 year
from dual
union all
select 6 empid, 900 sal, 2002 year
from dual
union all
select 6 empid, 1000 sal, 2003 year
from dual
union all
select 6 empid, 2000 sal, 2004 year
from dual
) select * from data
query i have tried
with data as
(select 1 empid, 500 sal, 2001 year
from dual
union all
select 1 empid, 400 sal, 2002 year
from dual
union all
select 1 empid, 600 sal, 2003 year
from dual
union all
select 2 empid, 500 sal, 2001 year
from dual
union all
select 2 empid, 700 sal, 2002 year
from dual
union all
select 2 empid, 800 sal, 2003 year
from dual
union all
select 2 empid, 900 sal, 2004 year
from dual
union all
select 3 empid, 800 sal, 2001 year
from dual
union all
select 3 empid, 500 sal, 2002 year
from dual
union all
select 3 empid, 200 sal, 2003 year
from dual
union all
select 3 empid, 700 sal, 2004 year
from dual
union all
select 3 empid, 500 sal, 2005 year
from dual
union all
select 4 empid, 800 sal, 2001 year
from dual
union all
select 4 empid, 900 sal, 2002 year
from dual
union all
select 4 empid, 1000 sal, 2003 year
from dual
union all
select 4 empid, 2000 sal, 2004 year
from dual
union all
select 6 empid, 900 sal, 2002 year
from dual
union all
select 6 empid, 1000 sal, 2003 year
from dual
union all
select 6 empid, 2000 sal, 2004 year
from dual
)
select empid
from (select a.*,
sum(rn3) over(partition by empid order by rn3) ern,
row_number() over(partition by empid order by year) rnk
from (select *
from (select empid,
sal,
year,
case
when sal > lead(sal, 1)
over(partition by empid order by year) then
0
else
1
end rn3
from data)) a)
where ern >= 3
and rnk <= 3
group by empid
expected output:
=========================================
[Edit MC: add code tags]
[Updated on: Thu, 27 April 2017 07:47] by Moderator Report message to a moderator |
|
|
|
| Re: Need to get the top 3 employees whose salary increased in three consecutive year [message #662377 is a reply to message #662374] |
Thu, 27 April 2017 07:57  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
LEAD/LAG functions have a second parameter which the offset from the current row:
SQL> with data as
2 (select 1 empid, 500 sal, 2001 year
3 from dual
4 union all
5 select 1 empid, 400 sal, 2002 year
6 from dual
7 union all
8 select 1 empid, 600 sal, 2003 year
9 from dual
10 union all
11 select 2 empid, 500 sal, 2001 year
12 from dual
13 union all
14 select 2 empid, 700 sal, 2002 year
15 from dual
16 union all
17 select 2 empid, 800 sal, 2003 year
18 from dual
19 union all
20 select 2 empid, 900 sal, 2004 year
21 from dual
22 union all
23 select 3 empid, 800 sal, 2001 year
24 from dual
25 union all
26 select 3 empid, 500 sal, 2002 year
27 from dual
28 union all
29 select 3 empid, 200 sal, 2003 year
30 from dual
31 union all
32 select 3 empid, 700 sal, 2004 year
33 from dual
34 union all
35 select 3 empid, 500 sal, 2005 year
36 from dual
37 union all
38 select 4 empid, 800 sal, 2001 year
39 from dual
40 union all
41 select 4 empid, 900 sal, 2002 year
42 from dual
43 union all
44 select 4 empid, 1000 sal, 2003 year
45 from dual
46 union all
47 select 4 empid, 2000 sal, 2004 year
48 from dual
49 union all
50 select 6 empid, 900 sal, 2002 year
51 from dual
52 union all
53 select 6 empid, 1000 sal, 2003 year
54 from dual
55 union all
56 select 6 empid, 2000 sal, 2004 year
57 from dual
58 )
59 select empid, sal, year,
60 lead(sal,1) over (partition by empid order by year) next_sal_1,
61 lead(year,1) over (partition by empid order by year) next_year_1,
62 lead(sal,2) over (partition by empid order by year) next_sal_2,
63 lead(year,2) over (partition by empid order by year) next_year_2,
64 lead(sal,3) over (partition by empid order by year) next_sal_3,
65 lead(year,3) over (partition by empid order by year) next_year_3
66 from data
67 /
EMPID SAL YEAR NEXT_SAL_1 NEXT_YEAR_1 NEXT_SAL_2 NEXT_YEAR_2 NEXT_SAL_3 NEXT_YEAR_3
---------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- -----------
1 500 2001 400 2002 600 2003
1 400 2002 600 2003
1 600 2003
2 500 2001 700 2002 800 2003 900 2004
2 700 2002 800 2003 900 2004
2 800 2003 900 2004
2 900 2004
3 800 2001 500 2002 200 2003 700 2004
3 500 2002 200 2003 700 2004 500 2005
3 200 2003 700 2004 500 2005
3 700 2004 500 2005
3 500 2005
4 800 2001 900 2002 1000 2003 2000 2004
4 900 2002 1000 2003 2000 2004
4 1000 2003 2000 2004
4 2000 2004
6 900 2002 1000 2003 2000 2004
6 1000 2003 2000 2004
6 2000 2004
19 rows selected.
Why 6 is not in the result set?
|
|
|
|
| Re: Need to get the top 3 employees whose salary increased in three consecutive year [message #662378 is a reply to message #662374] |
Thu, 27 April 2017 11:12 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Oracle version? You can use match recognize if you are on 12C:
with data as
(select 1 empid, 500 sal, 2001 year from dual union all
select 1 empid, 400 sal, 2002 year from dual union all
select 1 empid, 600 sal, 2003 year from dual union all
select 2 empid, 500 sal, 2001 year from dual union all
select 2 empid, 700 sal, 2002 year from dual union all
select 2 empid, 800 sal, 2003 year from dual union all
select 2 empid, 900 sal, 2004 year from dual union all
select 3 empid, 800 sal, 2001 year from dual union all
select 3 empid, 500 sal, 2002 year from dual union all
select 3 empid, 200 sal, 2003 year from dual union all
select 3 empid, 700 sal, 2004 year from dual union all
select 3 empid, 500 sal, 2005 year from dual union all
select 4 empid, 800 sal, 2001 year from dual union all
select 4 empid, 900 sal, 2002 year from dual union all
select 4 empid, 1000 sal, 2003 year from dual union all
select 4 empid, 2000 sal, 2004 year from dual union all
select 6 empid, 900 sal, 2002 year from dual union all
select 6 empid, 1000 sal, 2003 year from dual union all
select 6 empid, 2000 sal, 2004 year from dual
)
select empid
from data
match_recognize(
partition by empid
order by year
pattern(up{2,})
define
up as year = prev(year) + 1 and sal > prev(sal)
)
/
EMPID
----------
2
4
6
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: Need to get the top 3 employees whose salary increased in three consecutive year [message #662390 is a reply to message #662380] |
Fri, 28 April 2017 07:19 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
arpit0508 wrote on Fri, 28 April 2017 00:35
If there is hole in the years in such case those employee should not be come in query result.
with data as
(select 1 empid, 500 sal, 2001 year from dual union all
select 1 empid, 400 sal, 2002 year from dual union all
select 1 empid, 600 sal, 2003 year from dual union all
select 2 empid, 500 sal, 2001 year from dual union all
select 2 empid, 700 sal, 2002 year from dual union all
select 2 empid, 800 sal, 2003 year from dual union all
select 2 empid, 900 sal, 2004 year from dual union all
select 3 empid, 800 sal, 2001 year from dual union all
select 3 empid, 500 sal, 2002 year from dual union all
select 3 empid, 200 sal, 2003 year from dual union all
select 3 empid, 700 sal, 2004 year from dual union all
select 3 empid, 500 sal, 2005 year from dual union all
select 4 empid, 800 sal, 2001 year from dual union all
select 4 empid, 900 sal, 2002 year from dual union all
select 4 empid, 1000 sal, 2003 year from dual union all
select 4 empid, 2000 sal, 2004 year from dual union all
select 6 empid, 900 sal, 2002 year from dual union all
select 6 empid, 1000 sal, 2003 year from dual union all
select 6 empid, 2000 sal, 2004 year from dual
),
t as (
select empid,
count(*) over(partition by empid order by year range between 2 preceding and current row) cnt,
case
when sal > lag(sal) over(partition by empid order by year)
and
lag(sal) over(partition by empid order by year) > lag(sal,2) over(partition by empid order by year)
then 1
end sal_increase_flag
from data
)
select distinct empid
from t
where cnt = 3
and sal_increase_flag = 1
/
EMPID
----------
6
2
4
SQL>
Keep in mind, code isn't generic - we will need to modify it each time we want to change consecutive year number. For a generic solution use hierarchical query (although I'll weigh in performance if table is larger) or recursive subquery factoring.
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 13:36:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
