Skip to main content
May 30, 2008
Question

Position in a list

  • May 30, 2008
  • 2 replies
  • 339 views
running a reverse auction site and am trying to rank the bids in a list i.e. the lowest bid would be rank 1. however if the 2 lowest bids are the same they both come back as rank no 1. however i want it that if 2 bids in the list are the same then they will be ranked by date/time order. my code is:

<!--- Query to find all suppliers latest bid in the auction --->
<CFQUERY NAME="GetLatestBid" DATASOURCE="#Application.Datasource#">
SELECT Bids.SupplierID, Suppliers.Company, Bids.Bid
FROM Suppliers INNER JOIN Bids ON Suppliers.SupplierID = Bids.SupplierID JOIN
(Select SupplierID, MAX(Bid_Date) as Max_Bid_Date
FROM Bids
WHERE Bids.Auctionid=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
GROUP BY SupplierID) x ON Bids.SupplierID = x.SupplierID and Bids.Bid_Date = Max_Bid_Date
Order By Max_Bid_Date, Bid ASC;
</CFQUERY>

<!--- Rank the suppliers --->
<CFSET Position=ValueList(GetLatestBid.Bid)>
<CFLOOP LIST="#Position#" INDEX="i">
</CFLOOP>

<!--- Query to find this supplier's latest bid in the auction --->
<CFQUERY NAME="GetLatestBid2" DATASOURCE="#Application.Datasource#">
SELECT Bids.SupplierID, Suppliers.Company, Bids.Bid
FROM Suppliers INNER JOIN Bids ON Suppliers.SupplierID = Bids.SupplierID JOIN
(Select SupplierID, MAX(Bid_Date) as Max_Bid_Date
FROM Bids
WHERE (Bids.Auctionid=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.SupplierID=<CFQUERYPARAM VALUE="#SESSION.SupplierID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
GROUP BY SupplierID) x ON Bids.SupplierID = x.SupplierID and Bids.Bid_Date = Max_Bid_Date
Order By Max_Bid_Date, Bid ASC;
</CFQUERY>

<!--- Set the Rank/Position of each supplier --->
<CFSET Rank=ListFind(Position, #GetLatestBid2.Bid#)>

<!--- Display Rank --->
<CFOUTPUT>#Rank#</CFOUTPUT>
    This topic has been closed for replies.

    2 replies

    June 2, 2008
    got it working by replacing bid with supplierid in the list.
    Inspiring
    May 30, 2008
    You have the right idea. Would this work?

    select bids.supplierid, suppliers.company, bids.bid
    from suppliers inner join bids on suppliers.supplierid = bids.supplierid
    join
    (select supplierid, max(bid_date) as max_bid_date
    from bids
    where bids.auctionid=<cfqueryparam value="#url.auctionid#" cfsqltype="cf_sql_varchar" maxlength="50">
    group by supplierid
    ) x on bids.supplierid = x.supplierid and bids.bid_date = max_bid_date

    join
    (select supplierid, min(bid) low_bid
    from suppliers inner join bids on suppliers.supplierid = bids.supplierid
    where bids.auctionid=<cfqueryparam value="#url.auctionid#" cfsqltype="cf_sql_varchar" maxlength="50">
    group by supplierid
    ) y on suppliers.supplierid = y.suppliersid and bids.bid = low_bid
    where bids.auctionid=<cfqueryparam value="#url.auctionid#" cfsqltype="cf_sql_varchar" maxlength="50">

    order by low_bid, max_bid_date