Grouping query help


G

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
 
Ad

Advertisements

G

Guest

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

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.
 
Ad

Advertisements

G

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);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top