query to get highest bid from data

[gsc1ugs]

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

[jbisono]

try this

SELECT     TOP 1 idCustomerBid, idProduct, bidAmount, bidDate
FROM         bidHistory
WHERE     (idProduct = 272) AND (idCustomerBid <> 2) AND (idCustomerBid NOT IN
                          (SELECT     TOP 1 idCustomerBid
                            FROM          bidHistory AS bidHistory_1
                            WHERE      (idProduct = 272) AND (idCustomerBid <> 2)
                            ORDER BY bidAmount DESC))
ORDER BY bidAmount DESC

regards

[gsc1ugs]

Hi

Thxs for your reply, but i've found out i cant use idcustomerbid due to session timeouts, can this be reformed without it?

try this

SELECT     TOP 1 idCustomerBid, idProduct, bidAmount, bidDate
FROM         bidHistory
WHERE     (idProduct = 272) AND (idCustomerBid <> 2) AND (idCustomerBid NOT IN
                          (SELECT     TOP 1 idCustomerBid
                            FROM          bidHistory AS bidHistory_1
                            WHERE      (idProduct = 272) AND (idCustomerBid <> 2)
                            ORDER BY bidAmount DESC))
ORDER BY bidAmount DESC

regards

[jbisono]

oh im sorry i think i misunderstood the point. When you said this "the second highest bid that is not the same user " the same user means the person who is actually log in the system?

[gsc1ugs]

yes I think what is required is an if statement in a loop to get second highest by user, same bidder could bid 4 proxys higher than another bidder so that would mean 4 prices higher than the next bidder, is it possible to do ifs and loop with sql server 08?

[gsc1ugs]

I've solved this with following code

sql = "Select bidhistory.idCustomerBid as CustBid, bidhistory.idProduct, bidhistory.bidAmount, bidhistory.bidDate "
		sql = sql & "From   bidhistory "
		sql = sql & " where (bidhistory.idProduct = "&pidProduct&") order by bidAmount desc"

		set rsMin = Server.CreateObject("ADODB.Recordset") 
		rsMin.CursorLocation = 3
		rsMin.Open sql, conntemp
		
		if rsMin.RecordCount = 0 then
			pPrice = pListPrice		
		elseif rsMin.RecordCount = 1 then
			pPrice = pListPrice + IncrementTheBid()
		elseif rsMin.RecordCount > 1 then
			id = rsMin("CustBid")
			do while not rsMin.EOF				
				pPrice = pListPrice + IncrementTheBid()
				if rsMin("CustBid") <> id then
					pPrice = rsMin("bidAmount") + IncrementTheBid()
					Exit do
				end if
				id = rsMin("CustBid")
				rsMin.MoveNext			
			loop
		end if

[gsc1ugs]

Unless someone has a SQL Server 08 solution?

[Ahmad239]

you can use the distinct keyword in your query

[gsc1ugs]

no that will not work