SQL Server how to select first row in sequence

Question

I have a table with the following sample data:

Tag          Loc        Time1
A            10         6/2/15 8:00 AM
A            10         6/2/15 7:50 AM
A            10         6/2/15 7:30 AM
A            20         6/2/15 7:20 AM
A            20         6/2/15 7:15 AM
B            10         6/2/15 7:12 AM
B            10         6/2/15 7:11 AM
A            10         6/2/15 7:10 AM
A            10         6/2/15 7:00 AM

I need SQL to select the first (earliest) row in a sequence until location changes, then select the earliest row again until location changes. In other words I need the following output from above:

Tag         Loc         Time1
A           10          6/2/15 7:30 AM
A           20          6/2/15 7:15 AM
A           10          6/2/15 7:00 AM
B           10          6/2/15 7:11 AM

I tried this from Giorgos - but some lines from the select were duplicated:

declare @temptbl table (rowid int primary key identity, tag nvarchar(1), loc int, time1 datetime)
declare @tag as nvarchar(1), @loc as int, @time1 as datetime

insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:15 AM')
insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:20 AM')
insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:25 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:20 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:25 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:30 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:35 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:40 AM')

select * from @temptbl

SELECT Tag, Loc, MIN(Time1) as time2
FROM (
SELECT Tag, Loc, Time1,      
       ROW_NUMBER() OVER (ORDER BY Time1) - 
       ROW_NUMBER() OVER (PARTITION BY Tag, Loc 
                          ORDER BY Time1) AS grp
FROM @temptbl ) t

GROUP BY Tag, Loc, grp

Here is the results (there should only be one line for each tag)

Tag  Loc time2
1   20  2015-06-05 07:15:00.000
1   20  2015-06-05 07:25:00.000
4   20  2015-06-05 07:20:00.000
4   20  2015-06-05 07:30:00.000

Answer 1

In thinking about this, I wondered if there was a way to do it without using cursor. This seems to work:

SELECT t1.tag, t1.loc, t1.time1 
FROM( 
     SELECT tag, loc, time1, ROW_NUMBER() over (PARTITION BY tag ORDER by tag, 
     time1) AS row1 
     FROM @temptbl) t1
LEFT JOIN (
     SELECT tag, loc, time1, ROW_NUMBER() over (PARTITION BY tag ORDER by tag, 
     time1) AS row1 
     FROM @temptbl) t2
ON t1.row1=t2.row1+1 AND t1.tag=t2.tag
WHERE t2.tag IS NULL OR t1.loc <> t2.loc
ORDER BY tag, time1 DESC

Answer 2

This is an alternative solution in case your use versions prior to SQL Server 2012:

SELECT Tag, Loc, MIN(Time1)
FROM (
SELECT Tag, Loc, Time1,      
       ROW_NUMBER() OVER (ORDER BY Time1) - 
       ROW_NUMBER() OVER (PARTITION BY Tag, Loc 
                          ORDER BY Time1) AS grp
FROM mytable ) t
GROUP BY Tag, Loc, grp

It should work for SQL Server 2005+.

ROW_NUMBER() is used in order to identify islands of successive Tag, Loc records: grp will hold the same value for all records that belong to the same group.

EDIT: In case table row ordering is not based on Time1 column, then Time1 must be substituted by whatever column specifies row order, e.g. an auto-increment PK.

Demo here

Answer 3

Please give a try on this:

; with t --Samples
as (
select *
from (values 
('A',            10         ,'6/2/15 8:00 AM')
, ('A',            10         ,'6/2/15 7:50 AM')
, ('A',            10         ,'6/2/15 7:30 AM')
, ('A',            20         ,'6/2/15 7:20 AM')
, ('A',            20         ,'6/2/15 7:15 AM')
, ('B',            10         ,'6/2/15 7:12 AM')
, ('B',            10         ,'6/2/15 7:11 AM')
, ('A',            10         ,'6/2/15 7:10 AM')
, ('A',            10         ,'6/2/15 7:00 AM')
) t(Tag, Loc, Time1)
)
, t2 --get the origin order
as (
select *, ROW_NUMBER() over (order by (select null)) OriginOrder
from t)
, t3 --detect the tag, and loc changes (last row in group)
as (
select t2.*
from t2
    left join t2 t on t.OriginOrder=t2.OriginOrder+1 --get the next row
where not(t2.Tag = t.Tag and t2.Loc = t.Loc) --the next row has other tag and/or loc
    or (t.OriginOrder is null) --or there isn't next row
)
, t4 --make the groups about the next row
as (
select *, ROW_NUMBER() over (order by t3.OriginOrder) GroupOrder
from t3
)
, t5 --detect the tag, and loc changes (first row in group)
as (
select t2.*
from t2
    left join t2 t on t.OriginOrder=t2.OriginOrder-1 --get the previous row
where not(t2.Tag = t.Tag and t2.Loc = t.Loc) --the previous row has other tag and/or loc
    or (t.OriginOrder is null) --or there isn't previous row
)
, t6 --make the groups about the previous row
as (
select *, ROW_NUMBER() over (order by t5.OriginOrder) GroupOrder
from t5
)
--The result
select t2.Tag, t2.Loc, t2.Time1
from t4 
    join t6 on t4.GroupOrder=t6.GroupOrder
    cross apply (select top 1 * from t2 where t2.OriginOrder between t6.OriginOrder and t4.OriginOrder order by t2.Time1) t2

I comment my query, so I think it is understandable.

Answer 4

Can you try this, change yourTable with table name you want

declare @temptbl table (rowid int primary key identity, tag nvarchar(1), loc int, time1 datetime)

declare @tag as nvarchar(1), @loc as int, @time1 as datetime

declare tempcur cursor for 
select tag, loc, time1
from YourTable
-- order here by time or whatever columns you want to

open tempcur

fetch next from tempcur
into @tag, @loc, @time1

while (@@fetch_status = 0)
begin
    if not exists (select top 1 * from @temptbl where tag = @tag and loc = @loc and rowid = (select max(rowid) from @temptbl))
    begin
        print 'insert'
        print @tag
        print @loc
        print @time1
        insert into @temptbl (tag, loc, time1) values (@tag, @loc, @time1)
    end
    else
    begin
        print 'update'
        print @tag
        print @loc
        print @time1
        update @temptbl
        set tag = @tag,
            loc = @loc,
            time1 = @time1
         where tag = @tag and loc = @loc and rowid = (select max(rowid) from @temptbl)
    end


    fetch next from tempcur
    into @tag, @loc, @time1
end

deallocate tempcur

select * from @temptbl

Answer 5

Assuming you're using MS SQL Server 2012 or newer, the lag window function will allow you to compare a row to the previous one:

SELECT tag, loc, time1
FROM   (SELECT tag, loc, time1,
               LAG (loc) OVER (PARTITION BY tag ORDER BY time1) AS lagloc
        FROM   my_table) t 
WHERE  loc != lagloc OR lagloc IS NULL