Re: 'Select' max from 2 tables and join

From: Moritz Klein <mklein_at_students.uni-mainz.de>
Date: Thu, 13 Oct 2005 01:55:36 +0200
Message-ID: <dik7ha$ba9$1@news1.zdv.uni-mainz.de>

Charles schrieb:
> 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"
Try this one:

select note, max1, max2
from
(
select max(date1) max1, max(date2) max2
from location l, elevation e
where l.pid = e.pid
), location
where date1 = max1;

hth, Moritz Received on Wed Oct 12 2005 - 18:55:36 CDT