need max value from sql query

Question

I have a view which has many columns including ID,home, year,player,resource in it. I want to pull unique home records having only maximum value in year column.

Below is the data from view and expected output.

Data

id  home  year     player   resource
---|-----|------|--------|---------
1  | 10  | 2009 | john   | 399 
2  | 11  | 2007 | juliet | 244
5  | 12  | 2009 | borat  | 555
3  | 10  | 2010 | john   | 300
4  | 11  | 2009 | juliet | 200
6  | 12  | 2008 | borat  | 500
7  | 13  | 2004 | borat  | 600
8  | 13  | 2009 | borat  | 700

Expected output (unique home records having max year)

id  home  year     player   resource
---|-----|------|--------|---------
5  | 12  | 2009 | borat  | 555
3  | 10  | 2010 | john   | 300
4  | 11  | 2009 | juliet | 200
8  | 13  | 2009 | borat  | 700

Please suggest best way to do it.

Can you at least give us your table schema and the fields you are referring to. — neildt, Jan 12 '14 at 10:58
Downvoted because: It seems you are not the first one with exactly this homework (https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1) and you did not even try to program anything or to search. — Werner Henze, Jan 17 '14 at 12:07

score 2 · Answer 1 · answered Jan 17 '14 at 12:05

2

Try this:

WITH cte AS (
   SELECT home, MAX(year) AS year FROM Table1 GROUP BY home
)
SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year

Live demonstration: http://sqlfiddle.com/#!3/678771/8/0

answered Jan 17 '14 at 12:05

SysDragon

9,692
15
60
89

need max value from sql query

1 Answers1