Retrieve Last 10 Records with where Clause

# Retrieve Last 10 Records with where Clause

• Tuesday, January 08, 2013 5:59 AM

Hi,

Hi,
Please look into Table & I have a Query...
I need a sum of Income of Last(Latest) 10 Records of every Employee of only Those Month where Leave Status=0

Thanks,

### All Replies

• Tuesday, January 08, 2013 6:10 AM
Moderator

• Tuesday, January 08, 2013 6:12 AM

Try the below:(10 is made as 5 for easiness.)

```Drop table t1
create Table t1(EID int,Income int,Date1 Date,Leave int)
Insert into t1
Select 123,10,GETDATE()-1,1
Union All
Select 123,10,GETDATE()-2,0
Union All
Select 123,10,GETDATE()-3,0
Union All
Select 123,10,GETDATE()-4,1
Union All
Select 123,10,GETDATE()-5,1
Union All
Select 123,10,GETDATE()-6,1
Union All
Select 123,10,GETDATE()-7,1
Union All
Select 123,10,GETDATE()-7,1
Union All
Select 124,10,GETDATE(),1
Union All
Select 124,10,GETDATE()-1,0
Union All
Select 124,10,GETDATE()-2,1
Union All
Select 125,10,GETDATE()-1,1
Union All
Select 125,10,GETDATE()-2,1
Union All
Select 125,10,GETDATE()-3,1
Select A.EID,SUM(Income) From
(
Select *,ROW_NUMBER() Over(PARTITION by EID Order by Date1 desc) Rn From t1 Where Leave = 1
) A Where Rn<6
Group by A.EID```

• Tuesday, January 08, 2013 6:12 AM

Try

with set1 as
(select substring(convert(varchar,salmonth,112),1,6) mth
from hra
group by substring(convert(varchar,salmonth,112),1,6)
having max(leave)=0)
select top 10 *
from hra
where substring(convert(varchar,salmonth,112),1,6) in(select mth from set1)
order by salmonth desc;

Many Thanks & Best Regards, Hua Min

• Tuesday, January 08, 2013 6:16 AM

I'm not sure exactly what you are looking for.  But perhaps

```;With cteEmp As
(Select Distinct EmpID, EmpNm
From HRA),
cteIncome As
(Select EmpID, EmpNm, Income, Leave
Row_Number() Over(Partition By EmpID Order By SalMonth Desc) As rn
From HRA)
Select e.EmpID, e.EmpNm, Sum(i.Income) As SumOfIncome
From cteEmp e
Left Outer Join cteIncome i On e.EmpID = i.EmpID
And i.rn <= 10 And i.Leave = 0
Group By e.EmpID, e.EmpNm;```

Tom