Ordering and grouping with sticky entries at the bottom

[codebyren]

Hi.

 

My MySQL server version is 5.5.8.  I've got two tables something like these simplified ones:

 

authors:

 

author_id

name

1

Joe Bloggs

2

Mary Bloggs

3

John Smith

 

books:

(in this example, a book will only ever have 1 author):

 

book_id

author_id

title

price

discontinued

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

4

1

Average Book

8.00

0

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.

 

Thanks.

[Illusion]

select d.name,e.title,c.prc from authors d inner join books e on (d.author_id=e.author_id) inner join (

(select a. author_id a_id,min(a.price ) prc from books a where a.discontinued =0 group by author_id ) union

(select  b.author_id a_id,min(b.price ) prc from books b Where not exists(select count(*) cnt from books a where a.discontinued =0 and a.author_id=b.author_id group by b.author_id having cnt>0 ) and 

b.discontinued =1 group by b.author_id ))c on (c.a_id=e.author_id and e.price=c.prc) order by e.discontinued,c.prc asc

 

If author has more than one book with same price .. then this query will output all the books with that price for that author.

There can be more elegant solution and query can be optimized .

[codebyren]

Seems like it's more complicated than I expected.  The data set is quite large so I'm not sure I'll be able to go with this many joins etc. but I'll give it a shot.

 

Thanks for the reply.

[fenway]

1,2 and 3 should only require a single join.

[Illusion]

I think I over looked the logic ...including 4 also apart from inner join we need to get author name , we still need only one join

 

SELECT d.name, e.title, c.prc
FROM authors d
INNER JOIN books e ON ( d.author_id = e.author_id ) 
INNER JOIN (
(

SELECT author_id a_id, MIN( price ) prc, discontinued, COUNT( * ) AS cnt
FROM books a
WHERE a.discontinued =0
GROUP BY author_id
HAVING cnt >0
)
UNION (

SELECT author_id, price AS prc, discontinued, COUNT( * ) AS cnt
FROM  `books` 
GROUP BY author_id
HAVING cnt =1
AND discontinued =1
)
)c ON ( c.a_id = e.author_id
AND e.price = c.prc ) 
ORDER BY e.discontinued, c.prc ASC 

 

[fenway]

I meant without all of those subqueries.