Problems Querying Max Values From 2 Tables

[charles.amith]

I have 2 tables: LOCATION and ELEVATION

In location, I would like to find the record with the max value for
field: DATE1

In elevation, I would like to find the record with the max value for
field: DATE2

I would like to join the 2 tables using the common field: PID , and
also query a field called NOTE from the "LOCATION" table.

I've tried:

"SELECT NOTE, (SELECT MAX(DATE1) FROM LOCATION) AS MAXDATE1, (SELECT
MAX(DATE2) FROM ELEVATION) AS MAXDATE2 FROM LOCATION,ELEVATION WHERE
LOCATION.PID=ELEVATION.PID"

So far, this doesn't work. I've tried variations, that have a result,
but the note is not in synch with the date values. Any suggestions?

Charles Amith

[jimfortune]

ch***********@gmail.com wrote:

I have 2 tables: LOCATION and ELEVATION

In location, I would like to find the record with the max value for
field: DATE1

In elevation, I would like to find the record with the max value for
field: DATE2

I would like to join the 2 tables using the common field: PID , and
also query a field called NOTE from the "LOCATION" table.

I've tried:

"SELECT NOTE, (SELECT MAX(DATE1) FROM LOCATION) AS MAXDATE1, (SELECT
MAX(DATE2) FROM ELEVATION) AS MAXDATE2 FROM LOCATION,ELEVATION WHERE
LOCATION.PID=ELEVATION.PID"

So far, this doesn't work. I've tried variations, that have a result,
but the note is not in synch with the date values. Any suggestions?

Charles Amith

This is a total shot in the dark, but try:

SELECT NOTE, (SELECT MAX(A.DATE1) FROM LOCATION AS A WHERE A.PID =
LOCATION.PID) AS MAXDATE1, (SELECT MAX(A.DATE2) FROM ELEVATION AS A
WHERE A.PID = ELEVATION.PID) AS MAXDATE2 FROM LOCATION, ELEVATION WHERE
LOCATION.PID = ELEVATION.PID;

I think the subqueries you have are oblivious to the WHERE LOCATION.PID
= ELEVATION.PID criterion allowing dates/records that don't correspond
with NOTE to be included in the subqueries. Maybe this will help lead
you to a solution.

James A. Fortune

[Rich P]

Hi Charlse,

Not sure what kind of dataset you are trying to generate, but how about
a Group By query?

SELECT location.Note, Max(location.Date1) AS MaxOfDate1,
Max(Elevation.Date2) AS MaxOfDate2
FROM location INNER JOIN Elevation ON location.PID = Elevation.PID
GROUP BY location.Note;

I tried the other queries from this post and came up with similar
results as the Group By query above. The nice thing about the Group By
query is that it is simpler since there are no subqueries.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[charles.amith]

Thanks James, this worked ! However, I did change "AS A" to just "A".

The problem I'm having now is if I try to query more things than the
dates and note.

When I query PID, it doesn't work. I'm not sure what the rules are,
but I just couldn't get it to work. How about if I want to query note,
note2, note3, and PID? Any suggestions?

[jimfortune]

ch***********@gmail.com wrote:

Thanks James, this worked ! However, I did change "AS A" to just "A".

The problem I'm having now is if I try to query more things than the
dates and note.

When I query PID, it doesn't work. I'm not sure what the rules are,
but I just couldn't get it to work. How about if I want to query note,
note2, note3, and PID? Any suggestions?

Rich P had a good idea but he was only guessing also because we
couldn't see exactly what you wanted. Post a small example of your
data tables along with the results you want. Someone should be able to
come up with something to do it. The example would make everything
more concrete and would allow us to see if subqueries are even required
or to suggest ways to add new information easily.

James A. Fortune

[charles.amith]

Here's a sample of 2 tables. The result I want to have is to select
PID,EASTING,NORTHING, ELEV1,ELEV2, and the max of DATE_S and DATE_A,
and join using PID. If possible, without using GROUP BY. I want to do
this globally, and then also for a specific record.
So globally, the result would be:

PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005
A13 6912121 1923232 224.01 224.41 12/03/2004 12/03/2004
P12 6911111 1922222 224.01 224.41 12/02/2004 1/03/2005
and for a specific record (ie. PID=121)
PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005

----------SAMPLE TABLE SET BELOW-------------

Example:
Table: LOCATION
field name: PID EASTING NORTHING DATE_S
row
1 P12 6911111 1922222 12/02/2004
2 121 6913232 1923212 12/02/2004
3 A13 6912121 1923232 12/03/2004
4 121 6913222 1923222 12/03/2003
5 121 6913227 1923223 12/03/2002
Table: ELEVATION
field name: PID ELEV1 ELEV2 DATE_A
row
1 121 222.01 222.41 7/02/2005
2 121 223.55 223.95 5/02/2003
3 A13 224.01 224.41 12/03/2004
3 P12 218.01 218.41 1/03/2005

Can somebody help me produce the query for this. So far all of the
examples given didn't produce the right results using an ASP report.
ji********@compumarc.com wrote:

Rich P had a good idea but he was only guessing also because we
couldn't see exactly what you wanted. Post a small example of your
data tables along with the results you want. Someone should be able to
come up with something to do it. The example would make everything
more concrete and would allow us to see if subqueries are even required
or to suggest ways to add new information easily.

James A. Fortune

[jimfortune]

ch***********@gmail.com wrote:

Here's a sample of 2 tables. The result I want to have is to select
PID,EASTING,NORTHING, ELEV1,ELEV2, and the max of DATE_S and DATE_A,
and join using PID. If possible, without using GROUP BY. I want to do
this globally, and then also for a specific record.
So globally, the result would be:

PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005
A13 6912121 1923232 224.01 224.41 12/03/2004 12/03/2004
P12 6911111 1922222 224.01 224.41 12/02/2004 1/03/2005
and for a specific record (ie. PID=121)
PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005

----------SAMPLE TABLE SET BELOW-------------

Example:
Table: LOCATION
field name: PID EASTING NORTHING DATE_S
row
1 P12 6911111 1922222 12/02/2004
2 121 6913232 1923212 12/02/2004
3 A13 6912121 1923232 12/03/2004
4 121 6913222 1923222 12/03/2003
5 121 6913227 1923223 12/03/2002
Table: ELEVATION
field name: PID ELEV1 ELEV2 DATE_A
row
1 121 222.01 222.41 7/02/2005
2 121 223.55 223.95 5/02/2003
3 A13 224.01 224.41 12/03/2004
3 P12 218.01 218.41 1/03/2005

Thanks for providing the sample data. It helps a lot. I should be
able to get you some SQL to try by tomorrow night. It doesn't look too
hard, but I'll be busy traveling to Chicago today.

James A. Fortune

[jimfortune]

ch***********@gmail.com wrote:

Here's a sample of 2 tables. The result I want to have is to select
PID,EASTING,NORTHING, ELEV1,ELEV2, and the max of DATE_S and DATE_A,
and join using PID. If possible, without using GROUP BY. I want to do
this globally, and then also for a specific record.
So globally, the result would be:

PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005
A13 6912121 1923232 224.01 224.41 12/03/2004 12/03/2004
P12 6911111 1922222 224.01 224.41 12/02/2004 1/03/2005
and for a specific record (ie. PID=121)
PID EASTING NORTHING ELEV1 ELEV2 DATE_S DATE_A
121 6913232 1923212 224.01 224.41 12/02/2004 7/02/2005

----------SAMPLE TABLE SET BELOW-------------

Example:
Table: LOCATION
field name: PID EASTING NORTHING DATE_S
row
1 P12 6911111 1922222 12/02/2004
2 121 6913232 1923212 12/02/2004
3 A13 6912121 1923232 12/03/2004
4 121 6913222 1923222 12/03/2003
5 121 6913227 1923223 12/03/2002
Table: ELEVATION
field name: PID ELEV1 ELEV2 DATE_A
row
1 121 222.01 222.41 7/02/2005
2 121 223.55 223.95 5/02/2003
3 A13 224.01 224.41 12/03/2004
3 P12 218.01 218.41 1/03/2005

Can somebody help me produce the query for this. So far all of the
examples given didn't produce the right results using an ASP report.

Your example wasn't too clear about ELEV1 and ELEV2 values so I took a
guess.

SELECT LOCATION.PID, LOCATION.EASTING, LOCATION.NORTHING,
ELEVATION.ELEV1, ELEVATION.ELEV2, (SELECT MAX(A.DATE_S) FROM LOCATION
AS A WHERE A.PID = LOCATION.PID) AS DATE_SMax, (SELECT MAX(DATE_A) FROM
ELEVATION WHERE PID = LOCATION.PID) AS DATE_AMax FROM LOCATION,
ELEVATION WHERE DATE_S IN (SELECT MAX(A.DATE_S) FROM LOCATION AS A
WHERE A.PID = LOCATION.PID) AND DATE_A IN (SELECT MAX(DATE_A) FROM
ELEVATION WHERE PID = LOCATION.PID) ORDER BY LOCATION.PID;

resulted in:
PID EASTING NORTHING ELEV1 ELEV2 DATE_SMax DATE_AMax
121 6913232 1923212 222.01 222.41 12/2/04 7/2/05
A13 6912121 1923232 224.01 224.41 12/3/04 12/3/04
P12 6911111 1922222 218.01 218.41 12/2/04 1/3/05

SELECT LOCATION.PID, LOCATION.EASTING, LOCATION.NORTHING,
ELEVATION.ELEV1, ELEVATION.ELEV2, (SELECT MAX(A.DATE_S) FROM LOCATION
AS A WHERE A.PID = LOCATION.PID) AS DATE_SMax, (SELECT MAX(DATE_A) FROM
ELEVATION WHERE PID = LOCATION.PID) AS DATE_AMax FROM LOCATION,
ELEVATION WHERE LOCATION.PID='121' AND LOCATION.DATE_S In (SELECT
MAX(A.DATE_S) FROM LOCATION AS A WHERE A.PID = LOCATION.PID AND
ELEVATION.DATE_A In (SELECT MAX(DATE_A) FROM ELEVATION WHERE PID =
LOCATION.PID)) ORDER BY LOCATION.PID;

resulted in:
PID EASTING NORTHING ELEV1 ELEV2 DATE_SMax DATE_AMax
121 6913232 1923212 222.01 222.41 12/2/04 7/2/05

I only tested using the values you show. Give them a try with your
data. Also, let me know if the query is too slow or if you need the
SQL to be simpler. For example:

SELECT PID, EASTING, NORTHING, (SELECT MAX(A.DATE_S) FROM LOCATION AS A
WHERE A.PID = LOCATION.PID) AS DATE_SMax, (SELECT MAX(DATE_A) FROM
ELEVATION WHERE PID = LOCATION.PID) AS DATE_AMax FROM LOCATION WHERE
DATE_S IN (SELECT MAX(A.DATE_S) FROM LOCATION AS A WHERE A.PID =
LOCATION.PID) ORDER BY PID;

works without any using any joins if you don't need to see the ELEV1
and ELEV2 values. Plus I concentrated on getting a solution first
rather than on optimization so there's probably a lot of room for
improvement. Be sure to test what happens when multiple PID's have the
same DATE_AMax and DATE_SMax values. That's not too hard to fix if it
causes a problem. Tomorrow I'll try to come up with something more
elegant (and correct!).

James A. Fortune

[jimfortune]

ji********@compumarc.com wrote:

... bunch of stupid stuff ... I must have been tired.

Try your 'AS A' = 'A' trick on:

SELECT * FROM ELEVATION, LOCATION WHERE ELEVATION.PID = LOCATION.PID
AND DATE_S = (SELECT MAX(A.DATE_S) FROM LOCATION AS A WHERE A.PID =
LOCATION.PID) AND DATE_A = (SELECT MAX(A.DATE_A) FROM ELEVATION AS A
WHERE A.PID = ELEVATION.PID);

That should give you all the information from the tables matching the
latest dates. Post back if that doesn't handle everything or if you
would like more help.

James A. Fortune