By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

SQL problem

P: n/a
Hi

Hopefully someone can help with the following. The SQL statement
below works correctly, selecting the maximum [completion date] for
each store (there will be several records for each store in the
InvestmentHistory table.

SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date]
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID];

However, i also want to select 2 other columns from the table:
[ProjectName] and [InvestmentType]. When i add these two columns in to
get:

SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date], InvestmentHistory.[Investment Type],
InvestmentHistory.ProjectName
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID], InvestmentHistory.[Investment
Type], InvestmentHistory.ProjectName;

the query returns all the data again, presumably because there are
different values in the other 2 columns. Basically i just want the
query to return the info related to the maximum completiton date for
each store.

any ideas how i can achieve this.
Thanks for any suggestions.
Paul

Feb 7 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I didn't test this, but it should work.

SELECT B.[Store ID], A.[ProjectName], A.[InvestmentType], B.[MaxDate]
FROM InvestmentHistory AS A
INNER JOIN
(SELECT [Store ID], Max([Completion
Date]) AS [MaxDate]
FROM InvestmentHistory
GROUP BY [Store ID]) AS B
ON A.[Store ID] = B.[Store ID] AND
A.[Completion Date] = B.[MaxDate]

Cheers,
Jason Lepack

However, i also want to select 2 other columns from the table:
[ProjectName] and [InvestmentType]. When i add these two columns in
to
get:
On Feb 7, 10:21 am, paulquinlan...@hotmail.com wrote:
Hi

Hopefully someone can help with the following. The SQL statement
below works correctly, selecting the maximum [completion date] for
each store (there will be several records for each store in the
InvestmentHistory table.

SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date]
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID];

However, i also want to select 2 other columns from the table:
[ProjectName] and [InvestmentType]. When i add these two columns in to
get:

SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date], InvestmentHistory.[Investment Type],
InvestmentHistory.ProjectName
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID], InvestmentHistory.[Investment
Type], InvestmentHistory.ProjectName;

the query returns all the data again, presumably because there are
different values in the other 2 columns. Basically i just want the
query to return the info related to the maximum completiton date for
each store.

any ideas how i can achieve this.
Thanks for any suggestions.
Paul

Feb 7 '07 #2

P: n/a
On 7 Feb, 17:18, "Jason Lepack" <jlep...@gmail.comwrote:
I didn't test this, but it should work.

SELECT B.[Store ID], A.[ProjectName], A.[InvestmentType], B.[MaxDate]
FROM InvestmentHistory AS A
INNER JOIN
(SELECT [Store ID], Max([Completion
Date]) AS [MaxDate]
FROM InvestmentHistory
GROUP BY [Store ID]) AS B
ON A.[Store ID] = B.[Store ID] AND
A.[Completion Date] = B.[MaxDate]

Cheers,
Jason Lepack

However, i also want to select 2 other columns from the table:
[ProjectName] and [InvestmentType]. When i add these two columns in
to
get:

On Feb 7, 10:21 am, paulquinlan...@hotmail.com wrote:
Hi
Hopefully someone can help with the following. The SQL statement
below works correctly, selecting the maximum [completion date] for
each store (there will be several records for each store in the
InvestmentHistory table.
SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date]
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID];
However, i also want to select 2 other columns from the table:
[ProjectName] and [InvestmentType]. When i add these two columns in to
get:
SELECT InvestmentHistory.[Store ID], Max(InvestmentHistory.[Completion
Date]) AS [MaxOfCompletion Date], InvestmentHistory.[Investment Type],
InvestmentHistory.ProjectName
FROM InvestmentHistory
GROUP BY InvestmentHistory.[Store ID], InvestmentHistory.[Investment
Type], InvestmentHistory.ProjectName;
the query returns all the data again, presumably because there are
different values in the other 2 columns. Basically i just want the
query to return the info related to the maximum completiton date for
each store.
any ideas how i can achieve this.
Thanks for any suggestions.
Paul- Hide quoted text -

- Show quoted text -
Thanks Jason, worked a treat.

Feb 8 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.