rowid over partition within a dataset

[Baron]

Is it possible to generate rowid over partition in a dataset?

In the example below, I need to see a counter within each department, in other words I need another column in which I can see Mary is 1st in accounting and John is 2nd in accounting, then the counter starts from 1 for the next depart ....

create or replace table mytest(depart varchar(10), employee varchar(10), salary double);

insert into mytest values

('accounting', 'John', 2800),

('accounting', 'Mary', 2450),

('technical', 'Sarah', 3100),

('technical', 'Wolfgang', 3300),

('admin', 'George', 4200)

select depart, employee, salary, sum(salary) over (partition by depart) from mytest order by depart, salary

[VolkerBarth · Accepted solution]

row_number() is your friend:

select depart,
   row_number() over (partition by depart order by salary) as emp_no_per_depart,
   employee, salary,
   sum(salary) over (partition by depart) as depart_salary_sum
from mytest
order by depart, salary;

[Baron · Accepted solution]

Thanks for the reply, it works exactly as I want.

One more question, I see that the order by salary is mandatory in the first over partition, does it but really matter? I mean can I write it also so?

select depart,

row_number() over (partition by depart order by depart) as

emp_no_per_depart,

employee, salary,

sum(salary) over (partition by depart) as depart_salary_sum

from mytest

order by depart, salary;

[VolkerBarth · Accepted solution]

Well, the ORDER BY within the window definition specifies how the rows are numbered within each partition. Using the same expression for PARTITION BY and ORDER BY is certainly legal – but would mean you do not really specify an order, and the query engine is free to number the rows within each partition randomly. That's similar to omitting the final ORDER BY.

Here's a sample with your OVER clause but a different final ORDER BY including the row number, and in my tests, now "John" is accidentally numbered 1 within the accounting department.

select depart,
   row_number() over (partition by depart order by depart) as emp_no_per_depart,
   employee, salary,
   sum(salary) over (partition by depart) as depart_salary_sum
from mytest
order by depart, emp_no_per_depart;

I can't tell whether that "vague order" is sufficient for you.

[Baron · Accepted solution]

Thanks Volker, I got it.

In my case it was enough to have any numbering within the same department (the order doesnt matter).

The point was that I need my application to respond once per department (i.e. only when emp_no_per_depart = 1).