RE: SQL question

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 20 Jan 2005 20:39:15 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A01E6B22F@MSXVS02.trivadis.com>

Hi

If I correctly understand your requirements analytic functions could be = a solution... give a look to the following queries...

SQL> SELECT empno, ename, sal FROM emp WHERE deptno =3D 20;

     EMPNO ENAME SAL
---------- ---------- ----------

      7369 SMITH             800
      7566 JONES            2975
      7788 SCOTT            3000
      7876 ADAMS            1100
      7902 FORD             3000

SQL> SELECT empno, ename, sal,
  2       max(sal) OVER () max_sal,
  3       row_number() OVER (ORDER BY sal DESC) rn,
  4       count(*) OVER () count

  5 FROM emp=20
  6 WHERE deptno =3D 20;

     EMPNO ENAME SAL MAX_SAL RN COUNT ---------- ---------- ---------- ---------- ---------- ----------

      7788 SCOTT            3000       3000          1          5
      7902 FORD             3000       3000          2          5
      7566 JONES            2975       3000          3          5
      7876 ADAMS            1100       3000          4          5
      7369 SMITH             800       3000          5          5

SQL> SELECT * FROM (
  2 SELECT empno, ename, sal,

  3         max(sal) OVER () max_sal,
  4         row_number() OVER (ORDER BY sal DESC) rn,
  5         count(*) OVER () count

  6 FROM emp
  7 WHERE deptno =3D 20
  8 ) WHERE rn =3D 1;

     EMPNO ENAME SAL MAX_SAL RN COUNT ---------- ---------- ---------- ---------- ---------- ----------

      7788 SCOTT 3000 3000 1 5
>How can I accomplish the following...the problem is a bit beyond my SQL
>skills:
>
>select
>a_id,
>c_name,
>c_id,
>c_level,
>e_d,
>t_c,
>t_l,
>a_s,
>g_t,
>s_x,
>s_x,
>c_a,
>t_cd,
>c_a,
>n_k
>from ch_st c
>where
>active =3D 1
>and c.a_id =3D 99999
>
>Based on two conditions(active and a_id =3D), the select will return 1 =
or
>more rows.
>
>The final output should list the columns in the select above, BUT only =
1
>row should be outpouted and that row should list a count of the number =
of
>rows matching the criteria and the max c_level in the rows returned and
>the associated columns.
>
>So, simple query above returns say 4 rows....final query should return =
1
>row and perform a count and a max on a column and only list the columns
>associated with that max value...
>
>I hope that makes sense...I know max, group_by, etc are in the mix, =
but...

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 20 2005 - 14:45:27 CST