SQL statement help request

Question

I am learning SQL with the Adventureworks2012 database.

I am trying to create a query to find all the employees which had a raise. Here are the first 6 rows of the result that I got:

First column is businessentityid and date of the raise.

BID Salary Raise Date
4   05.01.2002 00:00:00
4   01.07.2004 00:00:00
4   15.01.2006 00:00:00
16  20.01.2002 00:00:00
16  16.08.2003 00:00:00
16  01.06.2006 00:00:00

With the below statement:

select  
    RateChangeDate, Rate, BusinessEntityID
from 
    HumanResources.EmployeePayHistory
where 
    BusinessEntityID in (select BusinessEntityID
                         from HumanResources.EmployeePayHistory
                         group by BusinessEntityID
                         having count (*) > 1);

Now what I would like to do is to remove the first row for each result as that is the first salary when the person got hired and not the raise.

The only extra column that this table has is Rate which displays the rates for each date.

enter image description here

The picture is with the rate column added to the above Statement.

Thanks :)

Answer 1

If you want all but the first salary for an employee, then you should learn how to use window functions. If you are learning SQL, I would advise you to be learning them quickly, because they are very powerful and useful.

You do what you want using ROW_NUMBER():

select RateChangeDate, Rate, BusinessEntityID
from (select eph.*,
             row_number() over (partition by BusinessEntityID order by RateChangeDate desc) as seqnum
      from HumanResources.EmployeePayHistory eph
     ) eph
where seqnum > 1;

You can read about row_number() in the SQL Server documentation.