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

asked 12 Jan, 10:50

Baron's gravatar image

Baron
1.8k115126149
accept rate: 48%


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;
permanent link

answered 12 Jan, 11:21

Volker%20Barth's gravatar image

Volker Barth
39.1k353535805
accept rate: 34%

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;

(13 Jan, 03:00) Baron
Replies hidden

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.

(13 Jan, 04:23) Volker Barth

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).

(13 Jan, 05:44) Baron
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 12 Jan, 10:50

question was seen: 339 times

last updated: 13 Jan, 05:45