CODEWITHSUNDEEP
sqlsql-server
Sopan_deole
Sopan_deole

Reputation: 29

How does ordering by inside window function work?

CREATE TABLE employee 
(
     name varchar(10),
     gender varchar(30), 
     salary int
);

INSERT INTO employee 
VALUES ('Mark', 'male', '5000'),
       ('John', 'male', '4500'),
       ('Pam', 'female', '5500'),
       ('Sara', 'female', '4000'),
       ('Todd', 'male', '3500'),
       ('Mary', 'female', '5000'),
       ('Ben', 'male', '6500'),
       ('Jodi', 'female', '7000'),
       ('Tom', 'male', '5500'),
       ('Ron', 'male', '5000');

SELECT 
    name, gender, salary, 
    AVG(salary) OVER (PARTITION BY gender ORDER BY salary 
                      rows between unbounded preceding and unbounded following) AS ave_sal
FROM
    employee

My question is, in the output, the salary table is not ordered by when I use the 'rows' parameter. If I remove the 'rows' parameter it is properly ordered by. Can someone please tell me How does ordering by inside window function work?

Upvotes: 0

Views: 1112

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

There is no "order" in a SQL table (or in a result set) unless you explicitly use an ORDER BY clause. Also, the ROWS clause of your call to AVG() is excessive, because AVG() without an ORDER BY clause will default to the entire partition without bounds. So, you may just use:

SELECT name, gender, salary, AVG(salary) OVER (PARTITION BY gender) AS ave_sal
FROM employee
ORDER BY AVG(salary) OVER (PARTITION BY gender);

Note that window functions can be used in the ORDER BY clause, in addition to the SELECT clause.

Upvotes: 2

Related Questions