Randomly Selecting Multiple Records

Question

I have a table with both Male & Female genders (PK (Id), PersonsName, IsMale). I wish to get a random name but for both genders. Example:

Select Top 1 PersonsName 
From MyTable 
Where IsMale = '1' 
Order by NewID()

Select Top 1 PersonsName 
From MyTable 
Where IsMale = '0' 
Order by NewID()

How can I combine those statements so I return 2 records (1 male and 1 female name) from the one SQL statement?

I have seen on here in the past that someone just separated the two statements in the same SQL query, like so:

Select Top 1 PersonsName 
From MyTable 
Where IsMale = '1' 
Order by NewID();

Select Top 1 PersonsName 
From MyTable 
Where IsMale = '0' 
Order by NewID()

Each time the code is run I wish to have two totally random names and not two records of the same gender so I can populate the dataset and bind it to a ListView control. I just need the SQL statement

Possible duplicate of [Select n random rows from SQL Server table](http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) — miken32, Jan 13 '17 at 20:15

Scott Chamberlain · Answer 1 · 2017-01-13T20:11:49.737

3

What you are looking for is the UNION keyword

select PersonsName from (Select TOP 1 PersonsName From MyTable Where IsMale = '1' Order by NewID()) a
UNION ALL
Select PersonsName from (Select TOP 1 PersonsName From MyTable Where IsMale = '0' Order by NewID()) b

This will return 1 result set with 2 rows.

edited Jan 13 '17 at 20:11

answered Jan 13 '17 at 19:04

Scott Chamberlain

124,994
33
282
431

1

This fails and even if I put into SQL Management Studio it says incorrect Syntax near Union and other people say you can have only one order by statement. Another question here then says to embed the statements like: select * from (select top 1 ... order by newid()) as T Union select * from (select top 1... order by newid()) as T order by PersonsName ASC which seems to work – MyNameHere Jan 13 '17 at 19:57
@MyNameHere syntax fixed, just put the orderby in a subquery, You may want to use UNION ALL instead of UNION like the other example you found. if you don't use ALL you could get 1 row returned if you randomly picked a name that is both a boys name and a girls name. If you don't have the ALL it performs a DISTINCT on the result. – Scott Chamberlain Jan 13 '17 at 20:12
Thank you Scott, that's a great tip!! – MyNameHere Jan 13 '17 at 20:56

John Cappelletti · Answer 2 · 2017-01-13T20:07:29.037

2

You can also use Row_Number() and the WITH TIES clause. This will return one of each.

Select Top 1 with Ties *
 From  YourTable
 Order By Row_Number() over (Partition By IsMale Order by NewID())

On a side-note. Top 4 will give you two of each

edited Jan 13 '17 at 20:07

answered Jan 13 '17 at 19:06

John Cappelletti

79,615
7
44
66

score 0 · Answer 3 · answered Jan 13 '17 at 19:39

0

Use union keyword for avoiding same duplicates records. Union is a keyword, use for writing two sql query which return same type of records with same number of columns(columns names can be differ but alias should me same for two queries).

Select Top 1 PersonsName From MyTable Where IsMale = '1' Order by NewID() union Select Top 1 PersonsName From MyTable Where IsMale = '0' Order by NewID()

answered Jan 13 '17 at 19:39

Aditya Singh

11
1

1

Isn't this exactly same as Scott Chamberlain's answer, except you have union instead of union all -- and why would you try to remove the second row as a duplicate? – James Z Jan 13 '17 at 19:43

Randomly Selecting Multiple Records

3 Answers3