sql:selecting the third highest and fifth highest values 2

likeopensource · Dec 26, 2002

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 · Dec 27, 2002

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 · Dec 27, 2002

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

Code:

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

Code:

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

Hope that helps.

rycamor · Dec 27, 2002

Yes, the old syntax is

Code:

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 · Dec 27, 2002

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 · Dec 31, 2002

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

sql:selecting the third highest and fifth highest values 2

likeopensource

Programmer

Leland123

Programmer

stoolpigeon

Programmer

rycamor

Programmer

stoolpigeon

Programmer

likeopensource

Programmer

Similar threads

Part and Inventory Search

Sponsor