Re: need help with a query

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Sat, 15 Oct 2005 08:47:21 +0200
Message-ID: <1129358841.88@user.newsoffice.de>

Charles schrieb am 14.10.2005 in
<1129327420.981892.274990_at_o13g2000cwo.googlegroups.com>:

I don not understand completely so I coan only guess. There are 2 Tables that contain some rows with same PID and some values you want to produce a result that calculates max(EASTING), max(NORTHING), max(DATE_S) (please correct me if I am wrong - maybe you don't think about max but most actual) connected to a select to the second table, that results max(ELEV1), max(ELEV2) (both without relation to pid) and max(DATE_A) with relation to PID

select
  pid,
  MaxEasting,
  MaxNorthing,
  (select max(elev1) from elevation) MaxElev1,   (select max(elev2) from elevation) MaxElev2,   MaxDateD,
  MaxDateA
from
  (
    select

      pid,
      max(easting) MaxEasting,
      max(northing) MaxNorthing,
      max(date_s) MaxDateS
    from
      location
    group by
      pid

  ) L1
join
  (
    select

      pid,
      max(date_a) MaxDateA
    from
      elevation
    group by
      pid

  ) E1
on
  L1.pid=E1.pid

this is completely untested and I do not know whether it hits your task. But maybe you can use it as template

> 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:

This part I completely do not understand: Do you want to avoid group by at all or only for a part of it?

> 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
What queries you tried out? Maybe I get a better feeling of your wishes.
> reports in ASP.

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Sat Oct 15 2005 - 01:47:21 CDT