Getting the maximum value of records

Question

Hello All,

I am having a table called Withdrawals which has : Name, year, period, amount.

EX:

Name   Year    Period    Amount
--------------------------------
Jim    2010    1         100
Jim    2009    4         99
Jim    2009    3         17
Kim    2007    2         234
Ryu    2008    5         555

I was stuck that I can't get the latest value for the name & amount which has the recent year with the latest period.

I tried to use the query:

select max(year), max(period), name from withdrawarls

But I got wrong results.

So, how can I get the correct values:

Jim, 2010, 1, 100
Kim, 2007, 2, 234
RYU, 2008, 5, 555.

Answer 1

SELECT * 
FROM Withdrawals w
JOIN (Select Name, Max(year) year
      FROM Withdrawals
      group by name) t ON (t.Name = w.Name AND t.Year = w.Year)

Answer 2

select *  from @tbl t
where t.year in (select max(year) from @tbl group by name)

With data you provided

declare @tbl table
(
    name varchar(10),
    year varchar(10),
    period int,
    amount int
)

insert into @tbl  select 'Jim', '2010', 1, 100
insert into @tbl select 'Jim', '2009', 4, 99
insert into @tbl select 'Jim', '2009', 3, 17
insert into @tbl select 'Kim', '2007', 2, 234
insert into @tbl select  'RYU', '2008', 5, 555

select *  from @tbl t
where t.year in (select max(year) from @tbl group by name)

Answer 3

select max(year), max(period), name from withdrawals group by name;

When using aggregate functions (ie min, max, count) the non-aggregated column should be listed in the "group by" clause. In this case "name".

Answer 4

In MySQL and PostgreSQL:

SELECT  t.*
FROM    (
        SELECT  DISTINCT name
        FROM    mytable
        ) td
JOIN    mytable t
ON      (t.year, t.period) =
        (
        SELECT  year, period
        FROM    mytable ti
        WHERE   ti.name = td.name
        ORDER BY
                name DESC, year DESC, period DESC
        LIMIT 1
        )

In SQL Server:

SELECT  t.*
FROM    (
        SELECT  DISTINCT name
        FROM    mytable
        ) td
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable ti
        WHERE   ti.name = td.name
        ORDER BY
                year DESC, period DESC
        ) t

In SQL Server and Oracle:

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, period DESC) AS rn
        FROM    mytable
        ) t
WHERE   rn = 1

Answer 5

Let's hope the Period is smaller than 100:

SELECT w.Name, w.Year, w.Period, w.Value
FROM Withdrawals w,
(SELECT Name, MAX(Year * 100 + Period) as maxTime
 FROM Withdrawals
 GROUP BY Name) AS maxW
WHERE w.Name = maxW.Name
AND w.Year * 100 + w.Period = maxW.maxTime

The problem with max(Year), max(Period) is that "2010 1" is later than "2009 4", while max(Year), max(Period) would return "2010 4", which does not exist. "Year * 100 + Period" gives you a good sorting.