Random number generation based on condition in sqlserver

Question

Need to generate random number based on ID3 column. Have mentioned each rows logic in remarks. Note: This is a sample data I need to apply on big set of data

Is there any possibility to create a rand number or incrementing number based on condition.

IF condition passes then retain same else generate another one (rand +1)

Is ID1 really "X" or is this an incrementing value? How are we ordering the data? — James Casey
– James Casey, Commented Dec 14, 2016 at 11:18
Also, what version of SQL Server are you using? SQL 2012+ will have a different solution to 2005 for example. — iamdave
– iamdave, Commented Dec 14, 2016 at 11:44
Please read this for some tips on improving your question. It's also helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. sql-server-2014. Differences in syntax and features often affect the answers. — HABO
– HABO, Commented Dec 14, 2016 at 14:17
@JamesCasey ID1 you need not to worry about. but value is x not incrementing one — vignesh
– vignesh, Commented Dec 15, 2016 at 3:05

James Casey · Accepted Answer · 2016-12-14 11:53:23Z

2

This will give you an incrementing number with the behaviour you want, assuming ID1 is an incrementing number. If its not you will need some other way to order the data since order is inherent to the behaviour you want.

Here I have assumed we can order by ID1 since its not used anywhere else in the logic

create table #t
(
    ID1 INT,
    ID2 INT,
    ID3 INT
)

insert into #t(ID1, ID2, ID3) values(1,1,1),(2,1,1),(3,2,1),(4,2,31),(5,2,1),(6,2,1),(7,2,23),(8,2,31);

with c1 as 
(
    select ID1, ID2, ID3,
        case when ID3 != 1 or lag(ID3,1,2) over (order by ID1) != 1 then 1 else 0 end as IncrementHere
    from #t
)
select ID1, ID2, ID3, sum(IncrementHere) over (order by ID1 rows unbounded preceding) as IncrementingNumber
from c1

answered Dec 14, 2016 at 11:53

James Casey

2,5071 gold badge14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Casey Over a year ago

Since from your comment above ID1 is not incrementing, this will need amending with some other ordering - basically replace "order by ID1" with "order by {something else}". Data in a table has no inherant order, you may need to add an identity column to the table if there is not some other suitable ordering criteria.

Collectives™ on Stack Overflow

Random number generation based on condition in sqlserver

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related