0

I've multiple value saved in one table. Each value correspond to a year, for example:

YEAR      | NAME
2014/2015 | Udine
2014/2015 | Firence
2015/2016 | Milan
2015/2016 | Chievo

In my query I want return all the rows that have the maximum years, in particular 2015/2016. So I write:

SELECT MAX( years ) , name
FROM teams
WHERE country =  'Italy'
AND league LIKE  'Serie A%'

But this query return the last row not all rows available. I'm waiting this result=> Milan, Chievo NB: THE TABLE VALUES IS JUST AN EXAMPLE

Bender
  • 523
  • 6
  • 21
  • possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Kalle Aug 19 '15 at 09:00

2 Answers2

0

Do a NOT EXISTS to return a row only if no later row exists for that team:

SELECT years, name
FROM teams t1
WHERE t1.country =  'Italy'
  AND t1.league LIKE  'Serie A%'
  AND NOT EXISTS (select 1 from teams t2
                  WHERE t2.country =  'Italy'
                    AND t2.league LIKE  'Serie A%'
                    AND t2.name = t1.name
                    AND t2.years > t1.years)
jarlh
  • 42,561
  • 8
  • 45
  • 63
0

try

SELECT t1.years, t1.name
FROM teams t1
join teams t2 on t2.years = t1.years
where t1.years = (select max(t3.years) from teams t3)
group by t1.name
Ahmad Anwar
  • 67
  • 3