| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: need help with a query
"Charles" <charles.amith_at_gmail.com> a écrit dans le message de news: 1129327420.981892.274990_at_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.
|
Here's 2 solutions with and without "group by":
SQL> select * from location;
PID EASTING NORTHING DATE_S
----- ---------- ---------- ----------
P12 6911111 1922222 12/02/2004 121 6913232 1923212 12/02/2004 A13 6912121 1923232 12/03/2004 121 6913222 1923222 12/03/2003 121 6913227 1923223 12/03/2002
5 rows selected.
SQL> select * from elevation;
PID ELEV1 ELEV2 DATE_A
----- ---------- ---------- ----------
121 222.01 222.41 07/02/2005 121 223.55 223.95 05/02/2003 A13 224.01 224.41 12/03/2004 P12 218.01 218.41 01/03/2005
4 rows selected.
SQL> select l.pid, l.easting, l.northing, e.elev1, e.elev2, l.date_s, e.date_a 2 from ( select pid,
3 max(easting) keep (dense_rank first order by date_s desc) easting, 4 max(northing) keep (dense_rank first order by date_s desc) northing 5 max(date_s) date_s 6 from location 7 group by pid ) l, 8 ( select pid, elev1, elev2, max(date_a) date_a 9 from ( select pid, 10 max(elev1) over () elev1, 11 max(elev2) over () elev2, 12 date_a 13 from elevation ) 14 group by pid, elev1, elev2 ) e15 where e.pid = l.pid
121 6913232 1923212 224.01 224.41 12/02/2004 07/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 01/03/2005
3 rows selected.
SQL> select l.pid, l.easting, l.northing, e.elev1, e.elev2, l.date_s, e.date_a 2 from ( select distinct
3 pid, 4 max(easting) keep (dense_rank first order by date_s desc) 5 over (partition by pid) easting, 6 max(northing) keep (dense_rank first order by date_s desc) 7 over (partition by pid) northing, 8 max(date_s) over (partition by pid) date_s 9 from location ) l, 10 ( select distinct 11 pid, 12 max(elev1) over () elev1, 13 max(elev2) over () elev2, 14 max(date_a) over (partition by pid) date_a 15 from elevation ) e
121 6913232 1923212 224.01 224.41 12/02/2004 07/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 01/03/2005
3 rows selected.
Regards
Michel Cadot
Received on Sat Oct 15 2005 - 02:27:34 CDT
 |
 |