Oracle SQL LAG() function results in duplicate rows

Question

I have a very simple query that results in two rows:

SELECT DISTINCT
id,
trunc(start_date) start_date
FROM   example.table
WHERE ID = 1

This results in the following rows:

id    start_date
1     7/1/2012
1     9/1/2016

I want to add a column that simply shows the previous date for each row. So I'm using the following:

SELECT DISTINCT id,
                Trunc(start_date) start_date,
                Lag(start_date, 1)
                  over (
                    ORDER BY start_date) pdate
FROM   example.table
WHERE  id = 1 

However, when I do this, I get four rows instead of two:

id    start_date    pdate
1     7/1/2012      NULL
1     7/1/2012      7/1/2012
1     9/1/2016      7/1/2012
1     9/1/2016      9/1/2012

If I change the offset to 2 or 3 the results remain the same. If I change the offset to 0, I get two rows again but of course now the start_date == pdate.

I can't figure out what's going on

Answer 1

Use an explicit GROUP BY instead:

SELECT id, trunc(start_date) as start_date,
       LAG(trunc(start_date)) OVER (PARTITION BY id ORDER BY trunc(start_date))
FROM   example.table
WHERE ID = 1
GROUP BY id, trunc(start_date)

Answer 2

The reason for this is: the order of execution of an SQL statements, is that LAG runs before the DISTINCT.

You actually want to run the LAG after the DISTINCT, so the right query should be:

WITH t1 AS (
   SELECT DISTINCT id, trunc(start_date) start_date
   FROM   example.table
   WHERE ID = 1
)
SELECT *, LAG(start_date, 1) OVER (ORDER BY start_date) pdate
FROM   t1