I want to group by the last date

[offie]

First let me explain what my data is and what I want to get:


For every Project and every Trade there are multiple Bids
I want to get the range of the BidAmount so I came up with

SELECT dbo_Project.StatusCode, dbo_BidsReceivedDetail.TradeCode, dbo_BidsReceivedDetail.ProjectCode, dbo_BidsReceivedDetail.AlternateBidNumber,  
Count(dbo_BidsReceivedDetail.BidAmount) AS CountOfBidAmount, CCur(Val(Min(dbo_BidsReceivedDetail.BidAmount))) AS MinOfBidAmount, 
CCur(Val(Max(dbo_BidsReceivedDetail.BidAmount))) AS MaxOfBidAmount, 
CCur(Val((Max([dbo_BidsReceivedDetail].[BidAmount])-Min([dbo_BidsReceivedDetail].[BidAmount])))) AS Range

FROM dbo_Project INNER JOIN dbo_BidsReceivedDetail ON dbo_Project.ProjectCode = dbo_BidsReceivedDetail.ProjectCode

GROUP BY dbo_Project.StatusCode, dbo_BidsReceivedDetail.TradeCode, dbo_BidsReceivedDetail.ProjectCode, dbo_BidsReceivedDetail.AlternateBidNumber

HAVING (((dbo_Project.StatusCode)="AWD" Or (dbo_Project.StatusCode)="COM") 
AND ((dbo_BidsReceivedDetail.AlternateBidNumber)=0))
ORDER BY dbo_BidsReceivedDetail.TradeCode;

But then I realized that bids can be places on different days and I need to only show the last day. I want to do this all in one query if possible.

I tried to group by Last(dbo_BidsReceivedDetail.BidDate) but apparently that can't be done.

Any Ideas?

Thanks!

Here is an example of the data I am getting this from, I want to use only the highlighted values to get the range of bid amount

[June7]

Returning record based on the Last (or Maximum) value in a field by group requires a subquery or DMax or DLast domain aggregate function.

Review: http://allenbrowne.com/subquery-01.html#TopN

[offie]

How exaclty would I do this? To get a Last value you have to group by another field, hence you get 2 fields, from what I've read it seems that subqueries only work when you get one field.

I tried to add
(SELECT ProjectCode, Last(BidDate) AS LastOfBidDate
FROM dbo_BidsReceivedDetail AS Dupe
GROUP BY Dupe.ProjectCode) AS LastDate
to the SELECT of the main query and then GROUP BY LastDate,

I get an error about using a query "that can return more than one field without using the EXISTS reserved word in the main query's FROM clause."

Comments?

[June7]

The sql you posted doesn't follow the Top N example I referenced. That example does not use any aggregate functions and does not do any grouping. It does require a unique ID field.