Need Help in Selecting Random and Non Sequential Data

Question

Post reply

Need Help in Selecting Random and Non Sequential Data

jeganbenitto.francis

SSC Eights!

Points: 956
More actions
February 8, 2013 at 2:23 am

#394142

Hi All,
Can someone help me in selecting Random Data from a table an also not even a single data should be in sequence
For example in DB i have ID from 01 to 50
And if i give
Select top 10 * from tblname order by newID() , i am getting below result
01
03
05
04
08
10
15
13 and so on.
In above result set , though the numbers are not in order, it has 03 , 05 , 04 which contains the next or previous number in result.
But as per requirement , the result set should be as below.
01
05
03
07
11
09
15
21
17
32
19... In the result set , we should not have any numbers with it's next or previous numbers.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Since the numbers are no longer random, why not break the randomness rules again and use this very simple algorithm?

;WITH SampleData AS (

SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)

FROM (SELECT TOP 50 n = ROW_NUMBER() OVER(ORDER BY name) FROM SYS.columns) d

)

SELECT

TOP(10)

ID,

IDx = RIGHT(IDx,2)

FROM (

SELECT TOP(20)

ID,

IDx = RIGHT('00'+CAST(2*CAST(ID AS INT) AS VARCHAR(3)),3)

FROM SampleData

ORDER BY NEWID()

) d

WHERE ID <= 25

ORDER BY IDx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeganbenitto.francis SSC Eights! Points: 956 More actions · Answer 2

Hi Chris,

Thanks for you response, when i executed, i get below result.

IDIDx

0306

1224

1326

1836

1938

2346

2550

But in realtime, I have a column named ID and the real values in it are 3315901,3315902......3315999

When i give select top 25 ID from tbl , i should get Random non sequential list only from this list of IDs

Can you please advise on this.

Thanks in advance

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

How many rows does the table have?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeganbenitto.francis SSC Eights! Points: 956 More actions · Answer 4

The tbl is a varying table, sometimes it will have 100, sometimes 1000,

As of now it is having 150 Datas. (means 150 IDs in the sequence which i mentioned in previous thread)

Thanks in advance

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Try this:

;WITH SampleData AS (

SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)

FROM (SELECT TOP 99 n = ROW_NUMBER() OVER(ORDER BY name) FROM SYS.columns) d

),

CutOfSample AS (

SELECT TOP(30) -- fetch more rows than you need, I think 3x to be safe

ID

FROM SampleData

ORDER BY NEWID()

)

-- because a few rows will be lost here

SELECT TOP 10 *

FROM CutOfSample c

WHERE NOT EXISTS (SELECT 1 FROM CutOfSample ci WHERE ci.ID = c.ID-1 OR ci.ID = c.ID+1)

ORDER BY c.ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeganbenitto.francis SSC Eights! Points: 956 More actions · Answer 6

Okies, Now i get the Row Numbers from you query.

But i have a small silly doubt again on this,

How can i show the ID (which is more than 8 digit) for the corresponding ROW_NUMBER

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

jeganbenitto.francis (2/8/2013)
Okies, Now i get the Row Numbers from you query.
But i have a small silly doubt again on this,
How can i show the ID (which is more than 8 digit) for the corresponding ROW_NUMBER

Have a quick read of the link in my sig "please read this", it will show you how to set up sample data for folks to test their code against. In the absence of a sample data set, I made one:

;WITH SampleData AS (

SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)

FROM (SELECT TOP 99 n = ROW_NUMBER() OVER(ORDER BY name) FROM SYS.columns) d

),

All this does is generate 99 rows with a single column called ID populated with values '01' to '99'. It's not part of the solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden