CODEWITHSUNDEEP
sqloracle-databasewindow-functions
George Saca
George Saca

Reputation: 90

Can't use Partition by and select * in the same query

I have the following error to the code below: "FROM keyword not found where expected"

SELECT *, MAX(Salary)
OVER (PARTITION BY ID_DEPT ORDER BY Salary DESC ) R
FROM SG_EMPLOYEES;

but when I change the asterisk with the names of each column everything works fine. So I just want to understand the reason behind this.

Upvotes: 0

Views: 272

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

When you use * in Oracle, it must be qualified if any other expressions are being selected. So:

SELECT e.*,
       MAX(e.Salary) OVER (PARTITION BY e.ID_DEPT ORDER BY e.Salary DESC) as R
FROM SG_EMPLOYEES e;

Note that I'm a big fan of qualifying all column names.

Your query actually seems very strange. You don't need the ORDER BY clause:

SELECT e.*,
       MAX(e.Salary) OVER (PARTITION BY e.ID_DEPT) as R
FROM SG_EMPLOYEES e;

Your version is taking the cumulative maximum and then ordering the salaries from the highest to the lowest -- so the cumulative is the same as the overall max.

Upvotes: 4

Related Questions