1

I have a table of bids for an auction system:

BIDS

id user_id   bid   auction_id
1    3        1        1
2    4        2        1
3    4        1        1
4    3        3        1
5    3        2        1
6    3        2        1

I need to get the highest user bid for a given user in a given auction (the sum of bids up to the last bid made by THAT user, taking into consideration that a user might not have made a bid at all).

For example:

for user id=3, highest bid would be 11.
for user id=4, highest bid would be 4.
for user id=6, highest bid would be 0. (user didn't make any bids)

So far I have this query:

SELECT COALESCE( SUM( bid ), 0 ) as highest_bid
FROM BIDS
WHERE user_id = 3
AND auction_id = 1

But is returning just the sum all all bids made by that user only, and I need the sum of bids up to the last bid made by THAT user.

I hope I make sense.

Thank you!

ndm
  • 59,784
  • 9
  • 71
  • 110
karlosuccess
  • 843
  • 1
  • 9
  • 25
  • https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ looked for a SO dupe, but this has great detail –  Feb 23 '18 at 01:07
  • I still don't understand exactly what you want. Do you want the sum of all bids of a specific user in an auction except the last? – danilo Feb 23 '18 at 01:25
  • He wants the sum of all bids till ad including the latest bid of user x. I'm not that good in SQL as i wrote a long time no SQL so cant give an answer. But in my opinion its not logic to save just the amount added to the previous bid. Its more convinient to store the value of the new bid if the new bid is higher as highest previous bid for concerning action. It reduces calculations and db requests. I'm not sure if this works for you specific application. – CodeNinja Feb 23 '18 at 11:21

1 Answers1

0

Try this. Essentially Im getting the max_id for each user and getting the sums until that max_id.

I haven't tested this though.

SELECT
  user_id,
  (SELECT sum(bid)
   FROM BIDS
   WHERE id <= max_ids.id)
FROM
  (SELECT
     max(id) AS id,
     user_id
   FROM BIDS
   GROUP BY user_id) max_ids
Rahul
  • 903
  • 8
  • 16