Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Selecting 2nd MAX(value) from DB

Options
  • 20-03-2010 10:07pm
    #1
    Black_Knight
    Moderators Posts: 12,375 ✭✭✭✭


    As says in the title.

    Im trying to run an SQL query which queries 3 tables. One is for users, 1 is for items, and 1 is for bids. Not trying to rip off ebay at all :D.

    I want to be able to list out all items, their users who uploaded them, and the 2nd highest bid amount. Im currently using the MAX(bidAmount), but that only gives me the largest bidAmount. I want to list the 2nd largest bidAmount.

    Any suggestions?

    Ive seen somewhere where it returns the
    MAX(bidAmount) from Bids
    where bidAmount<(SELECT max(bidAmount) FROM Bids)

    Im assuming this returns the max bid of all the bids lower then the max bid. (if you understand).

    Cheers


Comments

  • Options
    #2
    Sparks
    Registered Users Posts: 40,038 ✭✭✭✭Sparks


    Assuming it's MySQL, SELECT * FROM Bids ORDER BY BidAmount LIMIT 1,2;
    That'll return just the second highest bid.

    If it's not MySQL, you may need something else depending on the LIMIT clause syntax for your database.


  • Options
    #3
    Black_Knight
    Moderators Posts: 12,375 ✭✭✭✭Black_Knight


    That will only display the 2nd highest bid off all the items. 1 result.

    I need it to display each item, and only the 2nd highest bid on each item. Many results.


  • Options
    #4
    Sparks
    Registered Users Posts: 40,038 ✭✭✭✭Sparks


    Well if you want the full SQL, post your full schema :D
    Seriously, I'm just illustrating the mechanism there, you want some form of JOIN or even a view for what you're looking for. I won't claim this is great SQL, but it's headed in the right direction (without a map, that's pretty okay):

    SELECT User.Name, Bids.BidAmount, Item.ID
    FROM User LEFT JOIN Item ON User.ID = Item.UserID
    LEFT JOIN (SELECT BidAmount FROM Bids WHERE Bids.ItemID = Item.ID ORDER BY Bids.BidAmount LIMIT 1,2);

    (Yeah, I hate subqueries too, but the mapping to a join or view's not obvious and it's not my problem, I'm busy playing with some python code right now)


Advertisement