Today, while going through Oracle Forum, I came across this really interesting solution!
Lets suppose, We have a table tab1 having following columns (never mind the data type):
Now let suppose I need to delete the duplicate rows in tab1 and leave only the minimum DateIn and TimeIn in that table by UseridIn.
If I had this problem I would have solved the problem as follows:
DELETE FROM tab1
WHERE rowid NOT IN (
WHERE (UseridIn, TO_DATE(TO_CHAR(DateIn,’YYYYMMDD’) || TimeIn,’YYYYMMDDHH24:MI:SS’)) IN
( SELECT UseridIn, MIN(TO_DATE(TO_CHAR(DateIn,’YYYYMMDD’) || timein,’YYYYMMDDHH24:MI:SS’))
GROUP BY UseridIn)
GROUP BY UseridIn
But I found solution provided by William Robertson interesting which was as follows:
WHERE rowid IN
( SELECT LEAD(rowid)
OVER(PARTITION BY useridin ORDER BY datein,timein)
FROM tab1 );
Where he is using LEAD analytic function (which gives the access to a row at a given physical offset) with OVER keyword, PARTITION it by useridin and order by datein and timein.
Now let’s check out, how it is working:
Now when I executed following sql
LEAD(rowid) OVER(PARTITION BY useridin ORDER BY datein,timein) lead_rowid
USE TIMEIN CURRENT_ROWID LEAD_ROWID
— ——– —————— ——————
U01 07:20:00 AAAOdoAAEAAAAKEAAC AAAOdoAAEAAAAKEAAB
U01 08:10:30 AAAOdoAAEAAAAKEAAB AAAOdoAAEAAAAKEAAA
U01 08:20:00 AAAOdoAAEAAAAKEAAA
U02 06:10:30 AAAOdoAAEAAAAKEAAF AAAOdoAAEAAAAKEAAE
U02 06:10:30 AAAOdoAAEAAAAKEAAE AAAOdoAAEAAAAKEAAD
U02 08:14:00 AAAOdoAAEAAAAKEAAD
As we can see for each “useridin”, result data is order in asc order by datain and timein. So minimum datein and timein for each useridin will be the first row in the resultset for each useridin if we partition it by useridin as shown above.
And this minimum row will be not any rows LEAD_ROWID as it is the first row for each resultset partition by useridin.
If you do not specify offset(as in case above), then default will be 1.
If you don not specify how to do order by, then its default will be ASC order.