Can analytic functions add or remove rows from a resultset?

Question

I'm a novice who is starting to learn about Oracle analytic functions (window functions).

I know from experience that it's important to fully understand how SQL mechanisms work when using them in queries. It can be extremely easy to make mistakes and produce incorrect results.

For example, a left join can propagate-out duplicate rows, if the relationship was 1:M, but the person writing the query didn't realize it. And for non-experts, GROUP BY can produce unexpected row counts, etc.

---

Question:

Are there any scenarios where analytic functions can add or remove rows from a resultset? Or can I safely assume that analytic functions only provide additional information as calculated columns -- and don't ever effect the row count?

• User1974: Order of a SQL Query

Answer 1

The short answer seems to be no.

• Window functions only add additional information to the resultset as an extra column. They don't ever add or remove rows from the resultset.

---

Source:

PluralSight course: Advanced SQL Queries in Oracle and SQL Server by Scott Hecht

What are Analytic Functions:

• The resulting row count is the same as the incoming row count.
• Last SQL operation performed.
• Performed on the results of the query.
• Not affected by GROUP BY, HAVING, or WHERE

Slide 1 | Slide 2

I would recommend that course to anyone who wants to learn more about Analytic Functions and other advanced SQL querying topics.