1

What is the mistake in this query?

    select department_id, first_name,
lag(first_name) over (partition by department_id order by salary rows between 1 preceding and 2 following), 
lead(first_name) over (partition by department_id order by salary rows between 1 preceding and 2 following) 
from employees order by department_id;

Error is:

ORA-00907: missing right parenthesis

But parenthesis is ok. What is the problem here?

GMB
  • 216,147
  • 25
  • 84
  • 135
LOP
  • 23
  • 2

1 Answers1

3

lag() and lead() do not accept a windowing clause (the rows between part). What they do is look at the "previous" and "next" row, so, if you think about it, a window frame just does not make sense from functional perspective.

From the documentation - emphasis mine:

Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

Further down in the documentation, lag() and lead() are listed without the asterisk (unlike, for example avg() or count().

Presumably, you just want:

select 
    department_id, 
    first_name,
    lag(first_name)  over (partition by department_id order by salary) lag_first_name, 
    lead(first_name) over (partition by department_id order by salary) lead_first_name
from employees 
order by department_id;

Note that you can offset lead() and lag() by passing a second argument to the function (which defaults to 1 when not specified), like:

    lead(first_name, 3)  -- get the "third" following first name
        over (partition by department_id order by salary) lag_first_name
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you. But I need to select not the second first name, I need the third first_name after current first name. I cant to do this? – LOP May 14 '20 at 21:37
  • @LOP . . . I would suggest that you ask a *new* question with sample data, desired results and an explanation of what you want to do. This answers the question that you asked here. – Gordon Linoff May 14 '20 at 21:45
  • @LOP: you can pass a second argument to `lead()` and `lag()` - see my edit. For a more precise answer, you would indeed need to ask another question, as explained by Gordon Linoff. – GMB May 14 '20 at 21:55