stephenjacob stephenjacob - 3 months ago 10
SQL Question

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

Answer

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

Well, to be more specific, this query is based on assumption that you execute your query everyday at midnight. If you execute it at - say - 7.00 am, you would have to modify it a little

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