CODEWITHSUNDEEP
oracle-databaseselectplsqlcountsequence
Jim
Jim

Reputation: 1

the number of same record until this row

Is it possible to retrieve current number of same record for each row by using oracle pl/sql?

For example, I have class table which consists of id, name, age columns

I want to have the sequence of student with the same name and age entering the class, assuming that id is countering up without altering data structure.

Thanks.

Regards,

Jim

Upvotes: 0

Views: 116

Answers (2)

APC
APC

Reputation: 146239

Not sure I entirely get what you're asking for; you have an odd turn of phrase. An example of input data and expected result is always useful.

Perhaps something like this:

select id, name, age
from   your_table
where (name, age) in 
   ( select name. age
     from your_table
     group by name, age
     having count(id) > 1 )
order by name, age, id
/

You could solve this with analytics. However, you still need an outer query to filter out the records which aren't duplicated, so I'm not sure what you'd gain:

select * from (
    select id, name, age
            , count(id) over (partition by name, age) as dup_count
    from   your_table )
where dup_count > 1
order by name, age, id
/

Upvotes: 1

Aitor
Aitor

Reputation: 3429

I'm not sure either, but it sounds to me something related to analytic functions. Take this as an example, look at srlno column, calculated using analytic functions:

SELECT empno, deptno, hiredate,
ROW_NUMBER( ) OVER (PARTITION BY
deptno ORDER BY hiredate
NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO
------ ------- --------- ----------
  7782      10 09-JUN-81          1
  7839      10 17-NOV-81          2
  7934      10 23-JAN-82          3
  7369      20 17-DEC-80          1
  7566      20 02-APR-81          2
  7902      20 03-DEC-81          3
  7788      20 09-DEC-82          4
  7876      20 12-JAN-83          5

More on analyltic functions: http://www.orafaq.com/node/55

Remember, is a better approach if you can achieve your goal with a SQL instead of PL/SQL.

Upvotes: 0

Related Questions