How to retrieve different data from same sql query while we execute query simultaneously?

Question

I want to retrieve different records from same query while we execute same query in two or three different windows.

EX. select top 10 * from Person

This query I will execute in two or three windows on same time and I want to retrieve different records in all result.

When you say "different records" do you mean you want the same two sets of 10 records each time, or do you want to get 10 random records each time the query executed? — alroc, Nov 28 '19 at 11:53
i want 10 - 10 different record from both execution. no one record are same in both result. — Navneet GVM, Nov 28 '19 at 12:05
Possible duplicate of [Select n random rows from SQL Server table](https://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) — Amira Bedhiafi, Nov 28 '19 at 12:23
When you say "no one record are in the same result" you mean that you cannot ever have overlap between the two sets? That changes the potential answers _significantly_. You need to clarify your requirements in the original post. For example "I need to return 10 records which have never been returned by this same query previously." But is that since the beginning of time, just in the past hour, or only for the current two executions? So many ways this could go depending on those requirements, with varying complexity. — alroc, Nov 28 '19 at 13:06

score 2 · Answer 1 · answered Nov 28 '19 at 11:53

2

Order By NewID will give you a random set of rows. However, they may well be the same rows in a different order (definately if 10 or less rows in the table)

SELECT TOP 10 * 
FROM Person
ORDER BY NewID();

answered Nov 28 '19 at 11:53

AntDC

1,807
14
23

score 0 · Answer 2 · answered Nov 28 '19 at 11:51

0

You have to put ORDER BY clause. Otherwise it will take some random values.

select top 10 * 
from Person
order by 1 ASC

And the second window, use this.

select top 10 * 
from Person
order by 1 DESC

answered Nov 28 '19 at 11:51

DineshDB

5,998
7
33
49

what i have to do if i want 3 different result? – Navneet GVM Nov 28 '19 at 12:09

Amira Bedhiafi · Answer 3 · 2019-11-28T12:23:31.173

0

To have 3 different results, use all the possible cases :

SELECT TOP 10 * FROM Person p ORDER BY 1 ASC
SELECT TOP 10 * FROM Person p ORDER BY 1 DESC
SELECT TOP 10 * FROM Person p ORDER BY NewID()

or :

select  * from Person  where IDPerson in 
(select top 10 IDPerson  from Person   order by newid())

edited Nov 28 '19 at 12:23

answered Nov 28 '19 at 12:18

Amira Bedhiafi

8,088
6
24
60

dataconsumer · Answer 4 · 2019-11-28T17:02:27.413

0

You can use between. For eg. select top 10 * from Person where PersonId between <val1> and <val2> Assuming you have an Id or column or something similar. You can change the range for between for your three or more queries.

edited Nov 28 '19 at 17:02

answered Nov 28 '19 at 15:56

dataconsumer

196
1
5

score 0 · Answer 5 · answered Nov 28 '19 at 16:10

If you don't care which rows you retrieve, only that there are no duplicates in your three queries, use the modulo on the table's ID:

select top 10 * from person where id % 3 = 0;
select top 10 * from person where id % 3 = 1;
select top 10 * from person where id % 3 = 2;

Or use some order to get the first, second, and third ten rows:

select top 10 * from person order by id offset 0 rows fetch next 10 rows;
select top 10 * from person order by id offset 10 rows fetch next 10 rows;
select top 10 * from person order by id offset 20 rows fetch next 10 rows;

The latter can also be achieved with ROW_NUMBER.

How to retrieve different data from same sql query while we execute query simultaneously?

5 Answers5