CFLOOP Order

[Guest]

running a reverse auction site where say there are 5 suppliers bidding against each other and they have several bids each. However I only want to show the last bid and ranking of each supplier instead of all bids for all suppliers. The code below will do this only it orders by supplier name and not by ranking order.

<CFLOOP INDEX="ListSuppliers" LIST="#Suppliers#">

<CFQUERY NAME="GetLastBid" DATASOURCE="#Application.Datasource#">
SELECT AuctionID, SupplierID, Bid, Rank
FROM Bids
WHERE (AuctionID=<CFQUERYPARAM VALUE="#URL.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR">) AND (SupplierID='#ListSuppliers#')
ORDER BY Scores.Rank ASC, Bids.Bid ASC;
</CFQUERY>

<CFSET LastBid=ListFirst(GetLastBid.Bid)>

<CFOUTPUT>
<tr align="center">
<td><div align="center"><font face="arial" color="FFFFFF" size="2">#GetLastBid.SupplierID#</font></div></td>
<td><font face="Arial" color="FFFFFF" size="2">£#DecimalFormat(LastBid)# (Overall Rank: #GetLastBid.Rank#)</font></td>
</tr>
</CFOUTPUT>

</CFLOOP>

[Dan_Bracuk]

Do you have a date or timestamp field in your table to identify the last bid? If not, do you have a bid_id field that autoincrements?

[Guest]

yes, i have both but i should have said that it is a weighted reverse auction where the supplier ranked 1st mightnt necessarily have the best (lowest) bid.

an example of what i want is:

Supplier4 - £375000 (Rank 1)
Supplier5 - £365000 (Rank 2)
Supplier3 - £385000 (Rank 3)
Supplier1 - £370000 (Rank 4)
Supplier2 - £390000 (Rank 5)

but i am getting the following:

Supplier1 - £370000 (Rank 4)
Supplier2 - £390000 (Rank 5)
Supplier3 - £385000 (Rank 3)
Supplier4 - £375000 (Rank 1)
Supplier5 - £365000 (Rank 2)

it needs to be ordered by rank.

[Dan_Bracuk]

This will get you the most recent bid.

select fields
from bid join
(select bid_id, max(datefield) thedatefield
from bid
where etc
group by bid_id) x on bid.bid_id = x.bid_id and bid.datefield = thedatefield

rest of query