query for second highest bid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query for second highest bid

Author

Topic

firejackmusic
Starting Member

15 Posts

Posted - 2009-11-24 : 04:47:34

Hi

I have this data


CustBid	idProduct	bidAmount	bidDate
1	272	165.00	2009-11-20 09:05:31.640
1	272	155.00	2009-11-20 09:05:25.480
4	272	95.00	2009-11-19 12:40:44.973
4	272	85.00	2009-11-19 12:40:40.377
1	272	85.00	2009-11-12 11:20:52.400
1	272	85.00	2009-11-12 11:20:52.400
4	272	75.00	2009-11-12 11:11:02.080
1	272	65.00	2009-11-12 11:20:20.170
1	272	45.00	2009-11-12 11:08:02.407
1	272	25.00	2009-11-12 11:05:06.663

and I want to be able to list the second highest bid that is not the same user so what i would like to display is 95 because bidder 1 has two higher so i dont want those proxy bids visible, what query could i use? have this so far


Select bidhistory.idCustomerBid as CustBid, bidhistory.idProduct, bidhistory.bidAmount, bidhistory.bidDate From bidhistory where (bidhistory.idProduct = 272) AND (idCustomerBid <> 2) order by bidAmount desc

prakum
Starting Member

16 Posts

Posted - 2009-11-24 : 07:03:30

select top 1 * from
(
select top 2 * from
(
select bidhistory.idCustomerBid as CustBid, bidhistory.idProduct, max(bidhistory.bidAmount) as bidamount, bidhistory.bidDate
from bidhistory
group by idCustomerBid,idProduct,bidDate
) as d

order by bidamount DESC

)as dd

order by bidamount

Praveen Kumar

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 07:43:49

Praveen will always posts complex queries for simple tasks..
Here, i have a simple query for u..

SELECT TOP 1 WITH TIES *
FROM bidhistory
WHERE bidAmount NOT IN(SELECT TOP 1 MAX(bidAmount) FROM admin1 GROUP BY CustBid)
AND bidAmount IN(SELECT MAX(bidAmount) FROM admin1 GROUP BY CustBid)

Balaji.K

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 07:44:57

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-24 : 08:10:48

quote:
Originally posted by kbhere

Praveen will always posts complex queries for simple tasks..
Here, i have a simple query for u..

SELECT TOP 1 WITH TIES *
FROM bidhistory
WHERE bidAmount NOT IN(SELECT TOP 1 MAX(bidAmount) FROM admin1 GROUP BY CustBid)
AND bidAmount IN(SELECT MAX(bidAmount) FROM admin1 GROUP BY CustBid)
ORDER BY bidAmount DESC

Balaji.K

Just edit the OP. No need to create one more thread.

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources