Re: How to query serial number in select statement ?
Date: Mon, 4 Oct 1999 15:54:59 +0200
Message-ID: <7tabl4$hqc$1_at_oceanite.cybercable.fr>
You can use this statement:
select nvl(b.nb,1) num, a.empno, a.sal
from (select count(*)+1 nb, a.empno, a.sal from emp a, emp b
where b.sal<a.sal group by a.empno, a.sal) b, emp a
where b.empno (+) = a.empno
order by a.sal
/
Unfortunatly when you have several employee with the same salary, you get the same number:
v734> select nvl(b.nb,1) num, a.empno, a.sal
2 from (select count(*)+1 nb, a.empno, a.sal from emp a, emp b
3 where b.sal<a.sal group by a.empno, a.sal) b, emp a
4 where b.empno (+) = a.empno
5 order by a.sal
6 /
NUM EMPNO SAL
---------- ---------- ----------
1 7369 800
2 7900 950
3 7876 1100
4 7521 1250
4 7654 1250
6 7934 1300
7 7844 1500
8 7499 1600
9 7782 2450
10 7698 2850
11 7566 2975
12 7788 3000
12 7902 3000
14 7839 5000
14 rows selected.
You can have different number with a more complicated query:
v734> select nvl(b.nb,1)+nvl(c.nb,0) num, a.empno, a.sal 2 from (select count(*) nb, a.empno, a.rowid from emp a, emp b
3 where b.sal=a.sal 4 and b.empno < a.empno 5 group by a.empno, a.rowid) c, 6 (select count(*)+1 nb, a.empno from emp a, emp b 7 where b.sal<a.sal group by a.empno) b, 8 emp a
9 where b.empno (+) = a.empno
10 and c.empno (+) = a.empno
11 order by a.sal
12 /
NUM EMPNO SAL
---------- ---------- ----------
1 7369 800
2 7900 950
3 7876 1100
4 7521 1250
5 7654 1250
6 7934 1300
7 7844 1500
8 7499 1600
9 7782 2450
10 7698 2850
11 7566 2975
12 7788 3000
13 7902 3000
14 7839 5000
14 rows selected.
-- Regards Michel white shadow <shadow_at_topsol.co.kr> a écrit dans le message : QHTI3.1604$Lg2.71286_at_news2.bora.net...Received on Mon Oct 04 1999 - 15:54:59 CEST
> I try querying emp table
>
> select rownum, empno, sal from emp
>
> ROWNUM EMPNO SAL
> ---------- ---------- ----------
> 1 7369 800
> 2 7499 1600
> 3 7521 1250
> 4 7566 2975
> 5 7654 1250
> 6 7698 2850
> 7 7782 2450
> 8 7788 3000
> 9 7839 5000
> 10 7844 1500
> 11 7876 1100
> 12 7900 950
> 13 7902 3000
> 14 7934 1300
>
> I want to order by sal
> select rownum, emp,sal from emp order by dal
>
> ROWNUM EMPNO SAL
> ---------- ---------- ----------
> 1 7369 800
> 12 7900 950
> 11 7876 1100
> 3 7521 1250
> 5 7654 1250
> 14 7934 1300
> 10 7844 1500
> 2 7499 1600
> 7 7782 2450
> 6 7698 2850
> 4 7566 2975
> 8 7788 3000
> 13 7902 3000
> 9 7839 5000
>
> how to Query serial number order by select statement ?
>
>
>
>
>