globalwm2
asked on
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 R_MV
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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help.
ASKER
LAG(q1.ordered_date,1) OVER (PARTITION by EMAIL ORDER BY q1.ordered_date) prev_order_date