sql:selecting the third highest and fifth highest values 2

[likeopensource]

I have a conceptual problem with sql here

I have a table with 3 columns, auction_id, bidder_id, bid_price. The three together form a composite key.

After an auction is closed we have to select the third highest and fifth highest values.


The real life problem is a little different, but I am getting stuck with the issue I have mentioned above

 

[Leland123]

Hi likeopensource,

You might take a look at a postgres cursor. You could declare a cursor from your table using the limit = 5 to capture the top 5 bids. Once the cursor was in place you could fetch next, fetch previous, etc to go to the record containing the 3 highest bit and the 5th highest bid.

http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x15040.htm

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[stoolpigeon]

Or use limit with both parameters and then do 2 queries. For the 3rd bid it might look like

SELECT bid_price from foo order by bid_price desc LIMIT 1,2;

The first number on limit is how many results you want back- the second number is how many rows to skip. Your second query would be

SELECT bid_price from foo order by bid_price desc LIMIT 1,4;

Hope that helps.

 

[rycamor]

Yes, the old syntax is

LIMIT row, [offset]

.

But, note that the LIMIT clause has changed a little with latest versions of PostgreSQL. Now you must specify OFFSET with its own keyword (

http://www.postgresql.org/idocs/index.php?sql-select.html#SQL-LIMIT).

-------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Suspicion is Trust"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[stoolpigeon]

Thanx rycamor- saw that on the mailing list yesterday and completely spaced it. I'm holding off on 7.3 for a little while- let all the bugs get worked out.

 

[likeopensource]

Thanks ,the problem is solved
A couple of years earlier I got stuck in MS SQL Server solving this. Then last week I was asked this in an interview.