Member Avatar for 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
  • 3 Contributors
  • 8 Replies
  • 192 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by gsc1ugs

Dani AI

Generated

Brief summary of what the thread shows: wanted the highest bid from a different bidder (for product 272 the expected result is 95 because CustBid 1 holds the top bids). offered a nested TOP approach and ended up looping through a resultset in application code. suggested DISTINCT (which does not remove multiple bids from the same customer and so is not sufficient).

A compact, server-side solution for SQL Server 2008 uses window functions to (1) pick each customer’s top bid and then (2) rank those top bids to get the “next best distinct bidder.” This keeps the logic in a single query and avoids application loops:

WITH TopPerCustomer AS (
  SELECT idCustomerBid, idProduct, bidAmount, bidDate,
         ROW_NUMBER() OVER (PARTITION BY idCustomerBid ORDER BY bidAmount DESC, bidDate DESC) AS rn
  FROM bidHistory
  WHERE idProduct = @ProductID
),
RankedTopBids AS (
  SELECT idCustomerBid, idProduct, bidAmount, bidDate,
         DENSE_RANK() OVER (ORDER BY bidAmount DESC) AS priceRank
  FROM TopPerCustomer
  WHERE rn = 1
)
SELECT idCustomerBid, idProduct, bidAmount, bidDate
FROM RankedTopBids
WHERE priceRank = 2;   -- returns the next distinct top-bid by a different bidder

Notes and variants:

  • To exclude the currently logged-in bidder pass its id (e.g. @CurrentUserId) and add AND idCustomerBid <> @CurrentUserId in the final CTE filter so the best competing bid is returned.
  • Use DENSE_RANK() when ties by amount should collapse to the same rank; use ROW_NUMBER() if a stable tie-breaker (like bidDate) is required.
  • For large tables add a nonclustered index on (idProduct, idCustomerBid, bidAmount DESC, bidDate) or at least on (idProduct, idCustomerBid) to help grouping and window operations.
  • If no second distinct bidder exists, the query returns no rows; application-side fallback (list price + increment) is still appropriate in that case.

This keeps all logic in SQL Server and avoids the proxy-bid visibility issues that DISTINCT or simple TOP approaches fail to handle reliably.

Recommended Answers

All 8 Replies

Member Avatar for 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

Edited by jbisono because: n/a
Member Avatar for 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

Member Avatar for 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?

Member Avatar for 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?

Member Avatar for 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
Member Avatar for gsc1ugs

Unless someone has a SQL Server 08 solution?

Edited by gsc1ugs because: n/a
Member Avatar for Ahmad239

you can use the distinct keyword in your query

Member Avatar for gsc1ugs

no that will not work

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.