Count consecutive records using SQL

Question

I have a data analysis question that I could easily solve using some T-SQL or some scripts, but I was wondering if there is a smart SQL solution. The problem is that he is a little confused with the assumption of independence from the SQL string.

I have a table consisting of name-value pairs associated with the user and ordered by presentation, for example:

ID USERID VARIABLE VALUE SUBMITTED
3115 2287 votech05 2 2009-02-02 15:34:00
3116 2287 comcol05 1 2009-02-02 15:34:00
3117 2287 fouryr05 1 2009-02-02 15:35:00
3118 2287 none05 2 2009-02-02 15:35:00
3119 2287 ocol1_05 2 2009-02-02 15:44:00
3120 2287 disnone 2 2009-02-02 15:45:00
3121 2287 dissense 2 2009-02-02 15:49:00
3122 2287 dismobil 3 2009-02-02 15:51:00
3123 2287 dislearn 3 2009-02-02 15:51:00
3124 2287 disment 3 2009-02-02 15:52:00
3125 2287 disother 2 2009-02-02 15:55:00
3126 2287 disrefus 7 2009-02-02 15:58:00

I would like to be able to determine the value and quantity of the largest group of identical values ​​(when ordering data, the primary key is ID). So, for the above example, since I have four values ​​= 2 appearing in the sequence, and only three values ​​= 3, I would like to report:

USERID VALUE COUNT
2287 2 4

for this user.

, , ( 75 ) , . SQL Server 2005.

Answer 1

( )

, "" . .

, CTE :

WITH
OrderedTable as (
    select value, rownr = row_number() over (order by userid, id)
    from YourTable
    where userid = 2287
),
Heads as (
    select cur.rownr, CurValue = cur.value
    ,   headnr = row_number() over (order by cur.rownr)
    from OrderedTable cur
    left join OrderedTable prev on cur.rownr = prev.rownr+1 
    where IsNull(prev.value,-1) != cur.value
),
ValuesWithHead as (
    select value
    ,   HeadNr = (select max(headnr) 
                from Heads 
                where Heads.rownr <= data.rownr)
    from OrderedTable data
)
select Value, [Count] = count(*)
from ValuesWithHead
group by HeadNr, value
order by count(*) desc

:

Value   Count
2       4
3       3
1       2
2       1
2       1
7       1

"top 1", .

:

create table YourTable (
    id int primary key,
    userid int,
    variable varchar(25),
    value int
)
insert into YourTable (id, userid, variable, value) values (3115, 2287, 'votech05', 2)
insert into YourTable (id, userid, variable, value) values (3116, 2287, 'comcol05', 1)
insert into YourTable (id, userid, variable, value) values (3117, 2287, 'fouryr05', 1)
insert into YourTable (id, userid, variable, value) values (3118, 2287, 'none05', 2)
insert into YourTable (id, userid, variable, value) values (3119, 2287, 'ocol1_05', 2)
insert into YourTable (id, userid, variable, value) values (3120, 2287, 'disnone', 2)
insert into YourTable (id, userid, variable, value) values (3121, 2287, 'dissense', 2)
insert into YourTable (id, userid, variable, value) values (3122, 2287, 'dismobil', 3)
insert into YourTable (id, userid, variable, value) values (3123, 2287, 'dislearn', 3)
insert into YourTable (id, userid, variable, value) values (3124, 2287, 'disment', 3)
insert into YourTable (id, userid, variable, value) values (3125, 2287, 'disother', 2)
insert into YourTable (id, userid, variable, value) values (3126, 2287, 'disrefus', 7)

Answer 2

, . . , , CREATE TABLE INSERT , .

declare @userid int
set @userid = 2287;
declare C cursor fast_forward for
select VALUE from T
where USERID = @userid
order by ID;

declare @value int, @prevvalue int;
declare @runcount int, @runlongest int;
set @runlongest = 0;
declare @valuelongest int;
open C;
fetch next from C into @value;
while @@fetch_status = 0 begin
  if @value = @prevvalue set @runcount = @runcount + 1 else set @runcount = 1;
  if @runcount > @runlongest begin
    set @runlongest = @runcount;
    set @valuelongest = @value;
  end;
  set @prevvalue = @value;
  fetch next from C into @value;
end;
select @userid as USERID, @valuelongest as VALUE, @runlongest as [COUNT];

close C;
deallocate C;

75- , , , . , , , row_number temp, WHILE, . , , (, , CREATE TABLE INSERT ).

Answer 3

, :

Row_number() over ( , id)

, , row_nunber

, , .

, Edi