LAG OVER - not returning expected results when without a WHERE
I have this statement which returns expected results for a single email:
SELECT email, ordered_date,
LAG(ordered_date,1) OVER (ORDER BY ordered_date) prev_order_date
FROM APPS.XXOC_DISC_SALES_MASTE
WHERE EMAIL = 'TEDNUGENT@SNET.NET'
GROUP BY email, ordered_date
order by email, ordered_date
Returns
But when I remove the WHERE clause and run for all, the lag does not group as expected.
Any ideas?
SELECT email, ordered_date,
LAG(ordered_date,1) OVER (ORDER BY ordered_date) prev_order_date
FROM APPS.XXOC_DISC_SALES_MASTE
WHERE EMAIL = 'TEDNUGENT@SNET.NET'
GROUP BY email, ordered_date
order by email, ordered_date
Returns
But when I remove the WHERE clause and run for all, the lag does not group as expected.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Glad to help.
ASKER
LAG(q1.ordered_date,1) OVER (PARTITION by EMAIL ORDER BY q1.ordered_date) prev_order_date