Question
Position in a list
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>
<!--- 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>
