CODEWITHSUNDEEP
sqloracle-database
stephenjacob
stephenjacob

Reputation: 171

how to increment sql counter in select query

i want create a column 'counter' which will increase count whenever we run the select statement.

Suppose when i run the select query today. My output will be like below.

name    | employeeid | counter

raj        123             1
steve      124             1
brad       125             1

Tomorrow when i run the query the counter should gets increased for old records 

name    | employeeid | counter

raj        123             2
steve      124             2
brad       125             2
pitt       126             1

on the 3rd day when i run the counter should gets increased for old records like above.

name    | employeeid | counter

raj        123             3
steve      124             3
brad       125             3
pitt       126             2
camie      127             1

select name, employeeid, count(name) over (partition by name) counter from mytable orded by doj desc

Upvotes: 0

Views: 2233

Answers (2)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

If you're assuming that the query is executed once a day, then your counter is basically number of days between creation of the record and today + 1. Let's say the creation date is stored in creation_date column. You probably need something like:

select name, employeeid, 
       trunc(sysdate - creation_date) + 1 counter 
from your_table

Upvotes: 2

Suraj
Suraj

Reputation: 3137

For that you have to use trigger but the problem is that you can't use trigger on select statement.

But it could be achieve by using handler a PL/SQL procedure – that will process the Select Events that we want to be triggered on, then we can define a Fine Grained Auditing Policy that is associated with our Handler.

Reference : Click here

Hope it will help.

Upvotes: 2

Related Questions