SELECT row of last update with same value

Question

We have this table that keeps inserting values of Inrunning field which is a number.. There are multple mistakenly insert. I want to get the row where the first insert happened with duplicate value. In this case 5000 value inserted 3 times in matter of minutes. So i want to get the value first inserted which is Row 3.

What Oracle database version do you have? Run `select banner from v$version` if you don't know, and report the full version number, such as 11.2.0.3.0 or 12.2.0.1.0. Different solutions are available in different versions - the newer the version, the better the tools that you can use. — , Mar 18 '20 at 04:46

score 1 · Answer 1 · edited Aug 14 '23 at 19:39

1

You can use analytical function here as follows:

Select * from
(Select t.*, 
       Sum(case when inrunningdelay_1 = leadval then 1 end)
           over (partition by writer order by wrriten_1) as sm
 from
(Select t.*,
        lead(inrunningdelay_1) over (partition by writer order by wrriten_1) as leadval
from your_table t) t)
Where sm = 1;

edited Aug 14 '23 at 19:39

halfer

19,824
17
99
186

answered Mar 18 '20 at 03:33

Popeye

35,427
4
10
31

Barbaros Özhan · Answer 2 · 2020-03-18T12:59:38.833

If you're using Oracle DB 12c+, then you can use with no need to use an extra subquery through FETCH clause with 1 ROW ONLY option together with ORDER BY clause including LEAD() analytic function as

LEAD(inrunningdelay_1) OVER ( PARTITION BY writer ORDER BY written_1 )

starting from the value zero for

ABS( inrunningdelay_1 - LEAD(inrunningdelay_1) OVER ( PARTITION BY writer ORDER BY written_1 ) )

through use of absolute value for the difference

SELECT t.*
  FROM t   
 ORDER BY ABS( inrunningdelay_1 - LEAD(inrunningdelay_1) 
                                  OVER ( ORDER BY written_1 ) )
 FETCH FIRST 1 ROW ONLY

Demo

Update : PARTITION BY writer would be redundant within the above case, since that would work only for one writer as Tejash mentioned. For multiple writers, consider the below Select statement :

WITH t2 AS
(
  SELECT t.*, 
         ABS( inrunningdelay_1 - LEAD(inrunningdelay_1) 
             OVER ( PARTITION BY writer ORDER BY written_1 ) ) as ld            
    FROM t      
   ORDER BY writer, ld, written_1   
)
SELECT written_1, writer, inrunningdelay_1  
  FROM t2  
 ORDER BY ROW_NUMBER() OVER  ( PARTITION BY writer ORDER BY ld )
 FETCH FIRST 1 ROW WITH TIES

where you'd filter out the zero returned values only, and then would pick only the first returned ones for each writers by contribution of ROW_NUMBER() analytic function within the deepest ORDER BY clause :

Demo 2

This will work for only 1 writer. OP will not be able to find all such occurance using this for all the writers — Popeye, Mar 18 '20 at 05:48
@BarbarosÖzhan . . . Is that really new in Oracle 12C? Other analytic functions are permitted in the `ORDER BY`. — Gordon Linoff, Mar 18 '20 at 12:20
@GordonLinoff you're right, of course. I rearranged the order of the sentences to express that I meant `FETCH` clause for Oracle 12+. — Barbaros Özhan, Mar 18 '20 at 13:00

SELECT row of last update with same value

2 Answers2