My MySQL server version is 5.5.8. I've got two tables something like these simplified ones:
(in this example, a book will only ever have 1 author):
|1||3||Some Book Title||10.00||0|
|2||1||Another Great Book||15.00||0|
|3||1||Not Such A Great Book||15.00||1|
|5||2||The Penultimate Book||15.00||1|
|6||3||The Last Book||27.00||0|
My goal is to join these tables in a query that will:
1.) return a single row per author and
2.) the row must contain the author's cheapest book that is not discontinued and
3.) the rows must be sorted by price/author BUT
4.) if an author's ONLY book is discontinued, this row must appear last in the result set.
The catch for me is that when I include a GROUP BY `author_id` clause, any author that has ANY discontinued book whatsoever is getting pushed to the bottom of the result set. If they have any non-discontinued books, I need the cheapest one to be included instead (and sorted appropriately).
So far I've tried simple variations of:
SELECT *, MIN(price) AS min_price FROM authors JOIN books ON authors.author_id = books.author_id GROUP BY author_id ORDER BY discontinued ASC, min_price ASC
but as I say, the Joe Bloggs author with both the cheapest book and a separate, discontinued book would be listed last (undesirably) according to my query like the above.
I suspect it has to do with how GROUP BY determines which row to include but I've spent way too much time looking into this already and would appreciate some help.
I'd be happy to generate some dummy data if anyone wants to have a play around but I get the feeling I'm just missing something simple here.