SQL max() with inner joins

Question

I am creating an auction website. Here people can look for items and place bets on them. In the user account area I wish to have a list showing all the items where the user has placed a bid on.

Ofcourse each item can have more than one bet from different users so I only wish to show the one with the highest amount of money. This is so that the user can follow all the items on which he placed a bid on, and can track if he is still the one with the highest amount or not.

This is how my database looks like:

Tabel item                      Tabel itembid                          Tabel user
+=========================+     +================================+     +==============+
|id | title | description |     |id | item_id | user_id | amount |     |id | username |
+=========================+     +================================+     +==============+
| 1 | item1 | ........... |1   *| 1 |    1    |    2    |   10   |*   1| 1 |    me    |
| 2 | item2 | ........... |-----| 2 |    1    |    1    |   15   |-----| 2 |  myself  |
| 3 | item3 | ........... |     | 3 |    2    |    3    |   5    |     | 3 |    I     |
+=========================+     | 4 |    2    |    1    |   10   |     +==============+
                                +================================+

So as shown above, I have 3 tables (item, itembid and user). As you can see user 'me' has placed 2 bid, once on item 1 and once on item 2. It turns out that he is also currently the one with the highest bids on both items.

Now user 'myself' placed a bid before on item1 and 'I' placed a bid on item2. However, they are not anymore the ones with the highest bid (user 'me' is). Now I need an SQL statement that gives me a list of information (title, description, amount, username) that is a list of all items where I once placed a bid on. If I am the one currently with the highest bid for that item, I need to see the title and description of the item together with the amount that I placed for my bet as well as my username. Now, if I am not the one with the highest bid, I stil want to see the information of that item but now with the amount and username of the one with the highest bid.

So an example, looking in the perspective of user 'me', I want to see:

> item1, 15, me
> item2, 10, me

Now for user 'myself' I wish to see:

> item1, 15, me

(since 'me' once placed a bid for item1 but is no longer the one with highest amount, user 'me' is)

This is what I have so far but isn't working quiet well...

SELECT i.title, i.description, ib.amount, u.username
FROM item i
INNER JOIN itembid ib ON i.id = ib.item_id
INNER JOIN user u ON ib.user_id = u.id
WHERE ib.amount = (SELECT max(amount) FROM itembid ib2 WHERE ib2.id = ib.id)
AND ib.user_id = 1

Is this an auction site or a poker site? Poker has bets, auctions have bids. — Barmar, May 30 '14 at 21:30
SELECT MAX(amount), i.title, u.username FROM itembid ibi, item i, user u WHERE i.id=ibi.item_id AND u,id=ibi.user_id GROUP BY ibi.user_id — bksi, May 30 '14 at 21:31
Ow my mistake, my english is not so good since it is not my native language :) but it's an auction site, so i'll change all the occurences of 'bets' into 'bids' — RazorAlliance192, May 30 '14 at 21:31
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barmar, May 30 '14 at 21:33
What RDBMS are you using? Some databases have easy ways to do this, others (like MySQL) require you to jump through hoops. — Barmar, May 30 '14 at 21:33
@bksi could you please explain where I can add the userid of the user i wish to retrieve this information? Using your statement in get everything... Barmar, still trying to figure out how i can apply it in my problem — RazorAlliance192, May 30 '14 at 21:49
SELECT MAX(amount), i.title, u.username FROM itembid ibi, item i, user u WHERE i.id=ibi.item_id AND u,id=ibi.user_id AND user_id=??? GROUP BY ibi.user_id — bksi, May 30 '14 at 21:56
I think you understand mysql, so i just gave you a direction — bksi, May 30 '14 at 21:57
It's amazing how frequently the [tag:greatest-n-per-group] type query is asked for on StackOverflow. — Bill Karwin, May 30 '14 at 22:30

Barmar · Accepted Answer · 2014-05-30T23:06:30.220

2

Here it is:

SELECT i.title, bmax.amount, u.username
FROM itembid AS b
JOIN (SELECT item_id, MAX(amount) AS amount
      FROM itembid
      GROUP BY item_id) AS bmax
    ON b.item_id = bmax.item_id AND b.amount = bmax.amount
JOIN item AS i ON i.id = b.item_id
JOIN itembid AS ubid ON ubid.item_id = i.item_id
JOIN user AS u ON u.id = b.user_id
WHERE ubid.user_id = :current_user

edited May 30 '14 at 23:06

answered May 30 '14 at 22:20

Barmar

741,623
53
500
612

Still nog 100% as I want it to be, but it's the closest solution I received so far. I'll try to figure out the last part where I can just add the userid of the user i wish to retrieve the information. Thanks anyway :) – RazorAlliance192 May 30 '14 at 22:40
Sorry, got confused by that part of your question. I think it's just a matter of adding a `WHERE` clause that filters based on whether `b.user_id` is yourself or not. – Barmar May 30 '14 at 22:44
nope, nothing is doing the trick... I just want to have the bidding information of a particular user. So when a user has placed a bid before on an item. Even if he is no longer the one with the highest amount I want it to show then the new highest amount + username of the user who bid more for that item. – RazorAlliance192 May 30 '14 at 22:55
Oh, I see, you just want to filter down to only the items that the given user has bid on. That should be an easy fix, give me a minute. – Barmar May 30 '14 at 22:57
1

See the additional join with `itembid AS ubid` and the `WHERE` clause at the end. – Barmar May 30 '14 at 23:00
millions of kudos to you! However I had to switch the additional join with the one underneath it since it gave an error on the alias 'i'. You define the alias on the second line. Other than that, you have my eternal gratitude! – RazorAlliance192 May 30 '14 at 23:05
Oops. You also could have joined on `ubid.item_id = b.item_id` instead. – Barmar May 30 '14 at 23:07

SQL max() with inner joins

1 Answers1

Linked

Related