1

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.
Improve this question

5 Answers 5

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I will try this and revert back. I hope that it will get the maximum year and the maximum period in the maximum year for each name. Thanks and will come back
0 
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)
Improve this answer

1 Comment

Thank you, but what about the period? if there are values 3 and 4, it should choose the 4. how do I take the maximum period for that maximum year?
0 
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)
Improve this answer

2 Comments

Replace '*' with column names you need
Thanks for your help, but what about the period? If there are two period 3 , 4, it should should the maximum period also, so what should I include?
0

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.

Improve this answer

Comments

0 
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".

Improve this answer

1 Comment

No, this will not work, because in each year there are different periods and I should take the maximum period in the maximum year for each name

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.