rowid over partition within a dataset

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

group-by

asked 12 Jan '22, 10:50

Baron
2.1k●138●151●178
accept rate: 48%

flat view

One Answer:

active answers oldest answers newest answers popular answers

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 '22, 11:21

Volker Barth
40.2k●362●550●822
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 '22, 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 '22, 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 '22, 05:44) Baron

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

group-by ×11

question asked: 12 Jan '22, 10:50

question was seen: 578 times

last updated: 13 Jan '22, 05:45

SAP SQL Anywhere

rowid over partition within a dataset

Follow this question

Related questions