Duplicate names in column A different dates in column b

[Guest]

Is there a way to query a table like this and get a return of only the latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005 11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.

 

[Guest]

Create a totals query. In the Totals row, set Group By for Column A and Max
for Column B.

 

[John Vinson]

Is there a way to query a table like this and get a return of only the latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005 11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.

A Subquery will do the trick:

SELECT tablename.[Column A], [tablename].[Column B]
FROM tablename
WHERE tablename.[Column B] =
(SELECT Max([X].[Column B]
FROM tablename AS X
WHERE [X].[Column A] = [tablename].[Column A]);

John W. Vinson[MVP]

 

[Guest]

Is there a way to query a table like this and get a return of only the latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005 11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.

A Subquery will do the trick:

SELECT tablename.[Column A], [tablename].[Column B]
FROM tablename
WHERE tablename.[Column B] =
(SELECT Max([X].[Column B]
FROM tablename AS X
WHERE [X].[Column A] = [tablename].[Column A]);

John W. Vinson[MVP]

Thanks, John,
But the results of that is the max date of all entrie in column A.
What I need is the max date of each entry in column A.

 

[Guest]

Is there a way to query a table like this and get a return of only the latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005 11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.

A Subquery will do the trick:

SELECT tablename.[Column A], [tablename].[Column B]
FROM tablename
WHERE tablename.[Column B] =
(SELECT Max([X].[Column B]
FROM tablename AS X
WHERE [X].[Column A] = [tablename].[Column A]);

John W. Vinson[MVP]

Lets try again, I need the max date of each name in column A.
The max date for Infrastructure Availability Managers
The max date for CNCC Support Services Etc. Etc.

 

[John Spencer]

Maybe I don't understand what you want, but how about something as simple as

SELECT ColumnA, Max(ColumnB) as Latest
FROM YourTable
GROUP BY ColumnA

Is there a way to query a table like this and get a return of only the
latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005
11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.

A Subquery will do the trick:

SELECT tablename.[Column A], [tablename].[Column B]
FROM tablename
WHERE tablename.[Column B] =
(SELECT Max([X].[Column B]
FROM tablename AS X
WHERE [X].[Column A] = [tablename].[Column A]);

John W. Vinson[MVP]

Lets try again, I need the max date of each name in column A.
The max date for Infrastructure Availability Managers
The max date for CNCC Support Services Etc. Etc.

 

[Guest]

You the man!
It's a beautiful thing.
Thanks, for the help and the patients.

Maybe I don't understand what you want, but how about something as simple as

SELECT ColumnA, Max(ColumnB) as Latest
FROM YourTable
GROUP BY ColumnA

On Thu, 24 Aug 2006 11:39:02 -0700, A388517

Is there a way to query a table like this and get a return of only the
latest
date for all distinct names in column A?

Column A
Column B
Infrastructure Availability Managers 02/07/2004
11:59:00 PM
Infrastructure Availability Managers 01/22/2005
11:59:00 PM
CNCC Support Services 07/30/2004
11:59:00 PM
CNCC Support Services 08/12/2006
11:59:00 PM

I only want Infrastructure Availability Managers 02/22/2005
11:59:00 PM
and CNCC Support Services 08/12/2006
11:59:00 PM.
Thanks in advance.


A Subquery will do the trick:

SELECT tablename.[Column A], [tablename].[Column B]
FROM tablename
WHERE tablename.[Column B] =
(SELECT Max([X].[Column B]
FROM tablename AS X
WHERE [X].[Column A] = [tablename].[Column A]);

John W. Vinson[MVP]

Lets try again, I need the max date of each name in column A.
The max date for Infrastructure Availability Managers
The max date for CNCC Support Services Etc. Etc.