How to determine the maximum value for each category in SQL?
My table has records like below:
ID EmpID 开发者_StackOverflow EffectiveDate PayElement Amount ComputeType AddDeduction
42 ISIPL001 2010-04-16 00:00:00.000 Basic 8000.00 On Attendance Addition
43 ISIPL001 2010-04-01 00:00:00.000 Con 2000.00 On Attendance Addition
44 ISIPL001 2010-04-01 00:00:00.000 HRA 2000.00 On Attendance Addition
54 ISIPL001 2011-01-01 00:00:00.000 Basic 15000.00 On Attendance Addition
55 ISIPL001 2011-01-01 00:00:00.000 Con 6000.00 On Attendance Addition
57 ISIPL001 2011-01-01 00:00:00.000 HRA 6000.00 On Attendance Addition
61 ISIPL001 2010-07-10 00:00:00.000 Basic 12000.00 On Attendance Addition
66 ISIPL001 2010-07-10 00:00:00.000 HRA 4200.00 On Attendance Addition
68 ISIPL001 2010-07-10 00:00:00.000 Con 5600.00 On Attendance Addition
I want the result display below:
i.e for each pay element available in my database, I need to record which is having maximum date for each pay element.
So my output should be like given below:
54 Basic 15000
55 Con 6000
57 HRA 6000
Try this:
SELECT ID,
PayElement,
Amount
FROM (
SELECT a.*,
RANK() OVER(PARTITION BY PayElement ORDER BY EffectiveDate DESC) AS rn
FROM <YOUR_TABLE> a
) a
WHERE rn = 1
;with cte as
(
select *,
row_number() over(partition by PayElement order by EffectiveDate desc) as rn
from YourTable
)
select
ID,
PayElement,
Amount
from cte
where rn = 1
Try this.
select
T.ID,
T.PayElement,
T.Amount
from
Test T inner join (select MAX(T_DATE.EffectiveDate) as MAX_DATE, T_DATE.PayElement from Test T_DATE group by T_DATE.PayElement) T_DATE on (T.PayElement = T_DATE.PayElement) and (T.EffectiveDate = T_DATE.MAX_DATE)
order by
T.ID
Select a.Id,
a.PayElement,
a.Amount
From dbo.YourTable a
Join
(
Select PayElement,
Max(EffectiveDate) as[MaxDate]
From dbo.YourTable
Group By PayElement
)b on a.PayElement = b.PayElement
And a.EffectiveDate = b.MaxDate
try something like
Select
a.ID, a.PayElement, a.Amount
From MyTable a
Inner Join (
Select PayElement, max(EffectiveDate) as MaxDate From MyTable Group By PayElement
) sub on a.EffectiveDate = sub.MaxDate and a.PayElement = sub.PayElement
select
Id, PayElement, Amount
from
YourTable a
inner join
(select
Id, PayElement, max(EffectiveDate) as EffectiveDate
from
YourTable
group by
PayElement, Id) b
on
a.Id = b.Id
精彩评论