SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Brisbane, AUS
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Second Highest Value from table?

    Hey guys, im in need of some help.

    My Table columsn atm are.

    bid_value(Int)
    listing_id(Int)
    bid_id(Unique)
    bid_time(DATETIME)

    Basically I have a sub table which holds an auction listing. Then I have a bid table to record all subsequent bids on those items. Items linked to the auction have the same listing_id number as the listing. The bid_id is just a unique number for each bid irrelevant of which listing they are linked to, and the value is the monetry value they are bidding.

    My query atm currently is:

    select max(bid_value) from trp_bids where listing_id='4'

    I know this works, as it returns the appropriate value. What I would like to do is return not the highest value in the table, but the second highest. Anyhelp would be much appreciated.

    Thanks all.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     select bid_value 
     from trp_bids
     where listing_id=4
     and bid_value < (
      select max(bid_value) from trp_bids where listing_id=4
     )
     order by bid_value desc
     limit 1
    this should return the values from bid_value that are below the max bid_value, and order them in descending order. the limit 1 returns the topmost value in the list.

    Don't use quote marks around a number since when that happens the database has to translate the number to a string and then back again, this will slow your query down.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there's an even easier way (doesn't require 4.1 for the subquery, either)

    Code:
    select bid_value 
      from trp_bids
     where listing_id = 4
    order 
        by bid_value desc
     limit 1,1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that Rudy. Sometimes things AREN'T as complicated as one would first guess. ;-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •