How to go about SELECT 1 FROM

Question

This is my query

This is another try:

Instead of selecting the MAX, is there anyway I can get hold of the Book details ???

I have tried changing to Book.* but it's erroring. Please can someone tell me how to go about doing so I can retrieve the Book information ??

What i am trying to do is to get the highest amount from one table and get the records associated to it

Thanks in advance

A description of your error would also help. When you try `Book.*`, do you add table `Book` to FROM? — gmc, Mar 31 '17 at 00:37

score 1 · Answer 1 · answered Mar 31 '17 at 00:40

Without knowing your schema best I can help with (based on your current query) is:

SELECT bk.*, a.maxDonations
  FROM Book bk
        INNER JOIN (SELECT b.bookID, MAX(lre.donationsCollected) as maxDonations
                      FROM Book b
                            INNER JOIN MemberBorrowsBook mbb
                                    ON b.bookID = mbb.bookID
                            INNER JOIN LibraryRepairEvent lre 
                                    ON mbb.memberID = lre.organiserID
                     GROUP BY b.bookID
                    ) a
               ON bk.bookID = a.bookID

score 0 · Answer 2 · answered Mar 31 '17 at 00:40

This is giving you an error because you're trying to return every row from a query which is trying to give you an aggregate result.

The "max" operation is an aggregate function which means it says "give me a whole lot of rows, and I'll return you one result.

Whereas just saying "select *" isn't an aggregate operation, it just says "give me a result for each row that I select"

The easiest way to solve your problem and get all of the details for the row with max(donationsCollected) would be to do a select like:

select * from LibraryRepairEvent where donationsCollected =
SELECT MAX(LibraryRepairEvent.donationsCollected)
FROM LibraryRepairEvent
WHERE EXISTS (
    SELECT 1 FROM MemberBorrowsBook
    JOIN Book ON Book.bookID = MemberBorrowsBook.bookID
    WHERE LibraryRepairEvent.organiserID = MemberBorrowsBook.memberID
)

I can't remember if you need to say "where donationsCollected = " or "where donationsCollected in" so give them both a try

How to go about SELECT 1 FROM

2 Answers2