MySQL: SELECT highest column value when WHERE finds similar entries

Question

My question is comparable to this one, but not quite the same.

I have a database with a huge amount of books, with different editions of some of the same book titles. I'm looking for an SQL statement giving me the highest edition number of each of the titles I'm selecting with a WHERE clause (to find specific book series). Here's what the table looks like:

|id|title                    |edition|year|
|--|-------------------------|-------|----|
|01|Serie One Title One      |1      |2007|
|02|Serie One Title One      |2      |2008|
|03|Serie One Title One      |3      |2009|
|04|Serie One Title Two      |1      |2001|
|05|Serie One Title Three    |1      |2008|
|06|Serie One Title Three    |2      |2009|
|07|Serie One Title Three    |3      |2010|
|08|Serie One Title Three    |4      |2011|
|--|-------------------------|-------|----|

The result I'm looking for is this:

|id|title                    |edition|year|
|--|-------------------------|-------|----|
|03|Serie One Title One      |3      |2009|
|04|Serie One Title Two      |1      |2001|
|08|Serie One Title Three    |4      |2011|
|--|-------------------------|-------|----|

The closest I got was using this statement:

select id, title, max(edition), max(year) from books where title like "serie one%" group by title;

but it returns the highest edition and year and includes the first id it finds:

|--|-----------------------|-------|----|
|01|Serie One Title One    |3      |2009|
|04|Serie One Title Two    |1      |2001|
|05|Serie One Title Three  |4      |2011|
|--|-----------------------|-------|----|

This fancy join also comes close, but doesn't give the right result:

select b.id, b.title, b.edition, b.year from books b inner join (select name, max(edition) as maxedition from books group by title) g on b.edition = g.maxedition where b.title like "serie one%" group by title;

Using this I'm getting unique titles, but mostly old editions.

Solution 1

You can use a subquery:

select b1.id, 
  b1.title,
  b1.edition,
  b1.year
from books b1
inner join
(
  select max(edition) edition, title
  from books
  group by title
) b2
  on b1.edition = b2.edition
  and b1.title = b2.title

See SQL Fiddle with Demo

Or you can use an IN clause:

select id, title, edition, year
from books b
where edition in (select max(edition)
                  from books b1
                  where b.title = b1.title
                  group by title);

See SQL Fiddle with Demo

Solution 2

Here is a SQLFiddle example:

In MySql you can do this:

select id,title,edition,year from
(
      select id,title,edition,year,
      @i:=if(@title=title,@i+1,1) rn,
      @title:=title 
      from t, (select @i:=0,@title:='') d 
      order by title,edition Desc,year desc
) d where rn=1