CODEWITHSUNDEEP
sqloracle-database
rufanov
rufanov

Reputation: 3276

How can i skip records with same values that follow each other?

I have a table which stores bunch of records like this:

Time  UserName  
10:30   John   
10:40   John  
10:45   John  
11:05   Sara  
11:07   John   
11:08   Ned  
11:09   Ned

But I need to somehow skip records with same UserName as in previous record, so only record with first UserName will be in result, like so:

Time  UserName  
10:30   John   
11:05   Sara  
11:07   John   
11:08   Ned

It's very obvious how to do this in regular programming languages, but I can't understand how to do this in SQL.

Upvotes: 0

Views: 1026

Answers (4)

user5683823
user5683823

Reputation:

The query below uses the "gaps and islands" method (a.k.a. "tabibitosan" method) to group together CONSECUTIVE rows with the same username.

This only works if all times are distinct (otherwise the output from row_number() is not deterministic); but if the times are not all distinct, there is no "natural" sense of "consecutive" names anyway (in that case the problem itself needs to be clarified).

select min(time) as time, username from
    (select time, username,
            row_number() over (order by time) -
                   row_number() over (partition by username order by time) as gp
     from   inputs
    )
group by username, gp
order by time;

inputs is the base table.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think the simplest method is to use lag():

select t.time, t.name
from (select t.*,
             lag(name) over (order by time) as prev_name
      from t
     ) t
where prev_name is null or prev_name <> name;

I would imagine that this also has the best performance.

Upvotes: 3

Kostya
Kostya

Reputation: 1605

something like this

select t1.Time  ,t1.UserName from 
   ( select Time  ,UserName
    ,ROW_NUMBER() OVER (  ORDER BY Time,UserName ) R from table) t1
    join ( select Time  ,UserName
    ,ROW_NUMBER() OVER (  ORDER BY Time,UserName ) R from table) t2
    on t1.R = t2.R - 1
    where t1.UserName <> t2.UserName;

Upvotes: 1

Jay Kumar R
Jay Kumar R

Reputation: 537

This will work in all DBs without even analytic functions:

select * from temp t1 
where not exists ( select 1 from temp t2 
       where t2.UserName = t1.UserName
         and t2.Time < t1.Time 
         and not exists (select 1 from temp t3 
                where t3.UserName != t2.UserName 
                and t3.Time > t2.Time 
                and t3.Time < t1.Time 
                        ) 
                 ) ;

The inner query ensures there is no other same name that appears before another name in between.

Upvotes: 1

Related Questions