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