Duplicate names in column A different dates in column b


G

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

Advertisements

G

Guest

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

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]
 
G

Guest

John Vinson said:
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.
 
G

Guest

John Vinson said:
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.
 
J

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


A388517 said:
John Vinson said:
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.
 
Ad

Advertisements

G

Guest

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

John Spencer said:
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


A388517 said:
John Vinson said:
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.
 

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