Re: query help

From: Michael J. Moore <DoNThicamelSPAM_at_comcast.net>
Date: Tue, 27 Jul 2004 05:03:57 GMT
Message-ID: <1nlNc.35025$8_6.34163@attbi_s04>

>
> Analytics rock:
>
> SELECT DISTINCT FIRST_VALUE(el1) OVER(PARTITION BY el2 ORDER BY el3) el1,
> FIRST_VALUE(el2) OVER(PARTITION BY el2 ORDER BY el3) el2,
> FIRST_VALUE(el3) OVER(PARTITION BY el2 ORDER BY el3) el3
> FROM TEST;
>
> Look at autotrace output: less consistent gets, less sorts
>
> Urs

Given ...
SQL> select * from test;

EL1                         EL2        EL3
-------------------- ---------- ----------
2                            1          4
3                             2          4
4                             2          3
5                             2          3
0                             2          3
2                             2          3
1                             1          5

7 rows selected.

SQL> SELECT DISTINCT FIRST_VALUE(el1) OVER(PARTITION BY el2 ORDER BY el3) el1,

  2                  FIRST_VALUE(el2) OVER(PARTITION BY el2 ORDER BY el3)
el2,
  3                  FIRST_VALUE(el3) OVER(PARTITION BY el2 ORDER BY el3)
el3
  4             FROM TEST;

EL1                         EL2        EL3
-------------------- ---------- ----------
0                             2          3
2                             1          4

While .....
SQL> select * from test
2 where el1 in
3 (select min(el1) from test where(el2,el3) in 4 (select el2,min(el3) from test group by (el2) )group by (el2,el3));

EL1                         EL2        EL3
-------------------- ---------- ----------
0                             2          3
2                             1          4
2                             2          3

So, the nested select query fails to give the desired results when EL1 has non unique values. (two 2's)
The analytic query still gives the expected results. (well, at least the results I expected)
Mike Received on Tue Jul 27 2004 - 00:03:57 CDT