Sequential based on value of the query

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

How to make a sequential value based on Query?
Example, I have a query
that returns:

Select all

DATA 
---------- 
01/01/2006 
01/01/2006 
01/01/2006 
01/01/2006 
02/02/2006 
02/02/2006 
02/02/2006 
02/02/2006
Return

Select all

DATA             SEQ 
----------       --------------- 
01/01/2006    1 
01/01/2006    2 
01/01/2006    3 
01/01/2006    4 
02/02/2006    1 
02/02/2006    2 
02/02/2006    3 
02/02/2006    4

valeu!

[] 's
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Take a look at this example of Scott:

Select all

SELECT deptno, ename, sal , 
  ROW_NUMBER() 
    OVER (PARTITION BY deptno 
          ORDER BY ENAME) "Seq" 
FROM emp 
ORDER BY deptno, ename 
There goes his answer:

Select all

SQL> SELECT deptno, ename, sal , 
  2    ROW_NUMBER() 
  3      OVER (PARTITION BY deptno 
  4            ORDER BY ENAME) "Seq" 
  5  FROM emp 
  6  ORDER BY deptno, ename 
  7  / 
 
    DEPTNO ENAME             SAL        Seq 
---------- ---------- ---------- ---------- 
        10 CLARK            2450          1 
        10 KING             5000          2 
        10 MILLER           1300          3 
        20 ADAMS            1100          1 
        20 FORD             3000          2 
        20 JONES            2975          3 
        20 SCOTT            3000          4 
        20 SMITH             800          5 
        30 ALLEN            1600          1 
        30 BLAKE            2850          2 
        30 JAMES             950          3 
        30 MARTIN           1250          4 
        30 TURNER           1500          5 
        30 WARD             1250          6 
 
14 rows selected. 
 
SQL>  
Take a look at this topic Analytical functions: http://en.glufke.net/oracle/viewtopic.php?p=2028
: -O
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 20 guests