Second Highest Value from table?

[Carplace]

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.

[guelphdad]

 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.

[r937]

there's an even easier way (doesn't require 4.1 for the subquery, either)

select bid_value 
  from trp_bids
 where listing_id = 4
order 
    by bid_value desc
 limit 1,1

[guelphdad]

Thanks for that Rudy. Sometimes things AREN'T as complicated as one would first guess. ;-)