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 Go to next message
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:
Emp_id
2
4
=========================================



[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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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.
Now when you say "3 consecutive years", do you actually mean "years" or "rows"? or in another words what happens of there is a hole in the years? (for instance, if you change 2003 by 2004 and 2004 by 2005 in empid 2 rows).
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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 #662379 is a reply to message #662378] Thu, 27 April 2017 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version is in the title.

Re: Need to get the top 3 employees whose salary increased in three consecutive year [message #662380 is a reply to message #662379] Thu, 27 April 2017 23:35 Go to previous messageGo to next message
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
Junior Member
Thanks Michel/Solomon for your reply.
We need 'years'wise data.
If there is hole in the years in such case those employee should not be come in query result.
Empid 6 will also come in the result set(sorry i forget to mentioned it).
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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.
Re: Need to get the top 3 employees whose salary increased in three consecutive year [message #662412 is a reply to message #662390] Mon, 01 May 2017 03:05 Go to previous message
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
Junior Member
Thanks solomon for your solution and suggestion.
Previous Topic: COnditional change Serial
Next Topic: Inserting data in multiple tables
Goto Forum:
  


Current Time: Fri Feb 23 19:29:06 CST 2018

Total time taken to generate the page: 0.02103 seconds