need help with a query

From: Charles <charles.amith_at_gmail.com>
Date: 14 Oct 2005 15:03:41 -0700
Message-ID: <1129327420.981892.274990@o13g2000cwo.googlegroups.com>

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 I've received didn't produce the right results. I'm producing reports in ASP. Received on Fri Oct 14 2005 - 17:03:41 CDT