RE: SQL question
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <thump@xxxxxxxxxxxxxxxx>
- Date: Thu, 20 Jan 2005 20:39:15 +0100
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...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
