CODEWITHSUNDEEP
sqlsql-serverrankgaps-and-islandsrow-number
balaji dileep kumar
balaji dileep kumar

Reputation: 642

row_number numbering arbitrary value restart for value change

I have a table xxx with the following structure and columns (SQL Fiddle)

CREATE TABLE [dbo].[xxx]
(
    [GameNo] [int] IDENTITY(1,1) NOT NULL,
    [GameID] [varchar](500) NULL,
    [UserID] [int] NULL,
    [SteamID] [nvarchar](50) NULL,
    [GameWonLoose] [bit] NULL,
    [GameAbandon] [bit] NULL,
    [GamePlayDateTime] [datetime] NULL
)

having a unique constraint.

ALTER TABLE [dbo].[xxx] ADD UNIQUE NONCLUSTERED 
(
    [GameNo] ASC,[GameID] ASC )WITH (PAD_INDEX  = OFF, 
                                    STATISTICS_NORECOMPUTE  = OFF,  
                                    SORT_IN_TEMPDB = OFF, 
                                    IGNORE_DUP_KEY = OFF, 
                                    ONLINE = OFF, 
                                    ALLOW_ROW_LOCKS  = ON, 
                                    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                                    )

If I have constant bit value for GameWonLoose column(for 10 records that are continuous GameAbandon Status should be 0), I should select that Userid.

Sample records:

GameNo  GameID      UserID  SteamID             GameWonLoose    GameAbandon  GamePlayDateTime
---------------------------------------------------------------------------------------------------
1       Chennai1    20      steamID 1:165440    1               0           2013-12-25 4:41:25.300
2       Chennai2    20      steamID 1:165440    1               0           2013-12-25 14:41:25.310
3       Chennai3    20      steamID 1:165440    1               0           2013-12-25 14:41:25.317
7       Chennai4    20      steamID 1:165440    1               0           2013-12-25 14:50:17.617
8       Chennai5    20      steamID 1:165440    1               0           2013-12-25 14:50:17.623
9       Chennai6    20      steamID 1:165440    1               0           2013-12-25 14:50:17.633
10      chennai2    27      steamID 1:165222    1               0           2013-12-28 11:09:56.823
11      chennai2    27      steamID 1:165222    0               0           2013-12-28 11:12:17.043
12      chennai3    27      steamID 1:165222    1               0           2013-12-28 11:12:17.053
13      chennai4    27      steamID 1:165222    1               0           2013-12-28 11:12:17.063
14      chennai5    27      steamID 1:165222    1               1           2013-12-28 11:12:17.070
15      chennai6    27      steamID 1:165222    0               0           2013-12-28 11:12:17.080
16      chennai7    8       steamID 1:174502    1               0           2013-12-28 11:13:13.790
17      chennai8    8       steamID 1:174502    0               0           2013-12-28 11:13:13.797
18      chennai7    27      steamID 1:165222    1               0           2013-12-28 11:13:38.517
19      chennai8    27      steamID 1:165222    0               1           2013-12-28 11:13:38.523
20      chennai7    35      steamID 1:184002    1               0           2013-12-28 11:14:05.037
21      chennai8    35      steamID 1:184002    1               0           2013-12-28 11:14:05.047
22      chennai9    8       steamID 1:174502    1               0           2013-12-28 11:14:45.253
23      chennai10   8       steamID 1:174502    1               1           2013-12-28 11:14:45.263
24      chennai11   8       steamID 1:174502    0               0           2013-12-28 11:14:45.270
25      chennai9    35      steamID 1:184002    1               0           2013-12-28 11:15:04.740
26      chennai10   35      steamID 1:184002    1               1           2013-12-28 11:15:04.747
27      chennai11   35      steamID 1:184002    0               0           2013-12-28 11:15:04.757

For instance, UserId 20 has continuous bit value 1 for GameWonLose column where that gameAbandon column is 0. So I select that User Id (for 5 continuous record), if
that bit value for gamewonloose column changes to 0 and again to 1 it's not
considered. Here i need to find userid if it's true for any 5 records on the gameno asc order for that userid.

with x as
(
select GameNo,GameID,UserID, SteamID, GameWonLoose, GameAbandon,    
ROW_NUMBER()over(Partition by UserID Order by GameNo) as 'RowNumber' 
from xxx
)
select x.GameNo,x.GameID,x.UserID,x.SteamID,
x.GameWonLoose,x.GameAbandon,x.RowNumber,   
ROW_NUMBER()over(partition by x.UserId order by x.RowNumber),
ROW_NUMBER()over(partition by x.UserId order by x.RowNumber) 'RowNumber3' 
from x;

I tried with row_number to start numbering the row where the value for the
GameWonLoose bit column changes, but it starts with 2 when the value changes like
(1 0 1 10 0 0 0) like (1 1 2 3 2 3 4).

Upvotes: 0

Views: 563

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

See if GameWonLoose=1 (won),then how come in same row/record GameAbandon=1(means game cancel). Is it possible ?Have I followed ?Also if there is only one column called GameStatus(tinyint) 0-lose,1=won,2=game abondon etc.then it can be more manageable.

ok try this if it work,

;with cte as
    (select *,ROW_NUMBER()over(Partition by UserID order by gameno)rn from @xxx
    where GameWonLoose=1 and GameAbandon=0)


    select * from cte where userid in(select userid from cte group by userid having max(rn)>=5)

'Thanks for the answer'

for a particular userid if he/she is continuously winning 10 matches(gameno) i need to take that userid, in that game abandon is not considered only win lose is considered, like i win 3 matches and lose 1 and abandon one and again won 10 matches continuously without abanding, the last 10 wons will pick that userid

Upvotes: 1

Related Questions