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 name;
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.
