Grouping query help

[Guest]

I know I can do this using a sub query but I'm trying not to Basically I got
three feilds of data. One of the feilds is a dat feild and I'm trying to
group the data on the most recent date. For example: In the data below, I
would like the query to display for each category, the actual rate on the
most recent date (for that particular category).

Category Date Rate
CIT 4/1/2004 4
CIT 7/1/2004 4.25
CIT 8/9/2005 6.5
L30 11/10/2006 5.32
LAS 5/24/2004 4
LAS 7/1/2004 4.25
LAS 2/2/2005 5.5
LAS 3/22/2005 5.75
STD 5/24/2004 4
STD 7/1/2004 4.25
STD 9/21/2004 4.75
STD 11/10/2004 5

Desired Result
CIT 8/9/2005 6.5
L30 11/10/2006 5.32
LAS 3/22/2005 5.75
STD 11/10/2004 5

 

[Guest]

Try this ---
SELECT YourTable.Category, Max(YourTable.YourDate) AS MaxOfDate,
YourTable.Rate
FROM YourTable
GROUP BY YourTable.Category, YourTable.Rate;

 

[Guest]

Hey Karl,

Didn't work unfortunetly. Here's the SQL with the actual feild and table
names:

SELECT dbo_PrimeTable.sBankCode, Max(dbo_PrimeTable.dtEffectiveDate) AS
MaxOfDate,
dbo_PrimeTable.drate
FROM dbo_PrimeTable
GROUP BY dbo_PrimeTable.sBankCode, dbo_PrimeTable.dRate;

It's still listing out every record in the table and not grouping.

 

[Guest]

Sorry, did not test before posting. You can do it in two queries ---

Bdavis ---
SELECT dbo_PrimeTable.sBankCode, Max(dbo_PrimeTable.dtEffectiveDate) AS
MaxOfDate
FROM dbo_PrimeTable
GROUP BY dbo_PrimeTable.sBankCode;

SELECT dbo_PrimeTable.*
FROM dbo_PrimeTable INNER JOIN Bdavis ON (dbo_PrimeTable.dtEffectiveDate =
Bdavis.MaxOfDate) AND (dbo_PrimeTable.sBankCode = Bdavis.sBankCode);