CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7882

window function call requires an OVER clause

I have sql query that looks like this:

UPDATE people p
SET p.email = (SELECT 
                 MAX(FIRST_VALUE(email)) OVER (PARTITION BY person_id ORDER BY updated_at DESC)
               FROM person_emails pe
               WHERE pe.person_id = p.id);

it returns error:

 window function call requires an OVER clause

why?

Edit:

I've updated query to something like this:

UPDATE people p
  SET p.email = (
    SELECT MAX(
      FIRST_VALUE(email) OVER (PARTITION BY person_id ORDER BY updated_at DESC)
    )  
    FROM person_emails pe WHERE pe.person_id = p.id
  );

now it returns:

ERROR:  aggregate function calls cannot contain window function calls
LINE 4:       FIRST_VALUE(email) OVER (PARTITION BY person_id ORDER ...
              ^

Upvotes: 0

Views: 1821

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16497

Try:

UPDATE people p
  SET p.email = (
    SELECT MAX(first_email) 
    FROM (
     SELECT FIRST_VALUE(email) AS first_email, person_id OVER (PARTITION BY person_id ORDER BY updated_at DESC)
        FROM person_emails pe
    )  AS q
  WHERE q.person_id = p.id
  );

Upvotes: 3

Related Questions