choose a random record

Question

choose a random record

xRafo

SSChasing Mays

Points: 623
More actions
December 29, 2011 at 9:21 am

#262236

Hi Friends,
i have a question here:
how i can pick a record by random using TSQL..?
querying only one table (let's call tb_employees)
and show me the field name_emp
Thanks for the help!
and sorry for my bad english.
____________________________________________________________________________
Rafo*

Viewing 15 posts - 1 through 15 (of 21 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There are a few ways, depends how random you need it. This one's not particularly efficient, will give you a different row each time.

SELECT TOP (1) name_emp FROM employees ORDER BY NEWID()

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

xRafo SSChasing Mays Points: 623 More actions · Answer 2

Thanks!

if there is a more efficient would it be?

____________________________________________________________________________
Rafo*

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

How random do you need that row, and how large is the table? Is there an identity column?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

xRafo SSChasing Mays Points: 623 More actions · Answer 4

I just Need 1 row.

The table have around 2000 records.

Yes a column with the name "id" is the identity.

I've been testing the above query but sometimes give 2 times return the same value,

I need not repeat random at least 50 times, it's possible?

____________________________________________________________________________
Rafo*

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

xRafo (12/29/2011)
I just Need 1 row.
The table have around 2000 records.
I've been testing the above query but sometimes give 2 times return the same value,
I need not repeat random at least 50 times, it's possible?

Change the query to this:

SELECT TOP (50) name_emp FROM employees ORDER BY NEWID()

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

xRafo SSChasing Mays Points: 623 More actions · Answer 6

Hi,

I just need 1 row,

but with that query i executed 2 times and give me 2 same..

i need not repeat the result..

____________________________________________________________________________
Rafo*

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

How will the query know that the first result should be excluded the second time it runs? It's no longer random at that point, unless you put the selected names into a table and do some sort of check ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

xRafo SSChasing Mays Points: 623 More actions · Answer 8

how would you do that?,

maybe a good idea!

____________________________________________________________________________
Rafo*

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

xRafo (12/29/2011)
Hi,
I just need 1 row,
but with that query i executed 2 times and give me 2 same..
i need not repeat the result..

Place rand() * 100 function in your WHERE clause.

Example:

WHERE some_int_column= rand() * 100 -- or cast it as INT

RAND (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms177610.aspx

xRafo SSChasing Mays Points: 623 More actions · Answer 10

Hi,

thanks!

but the outcome of the query needs to be a character..

____________________________________________________________________________
Rafo*

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

xRafo (12/29/2011)
Hi,
thanks!
but the outcome of the query needs to be a character..

You can SELECT a character column of your choice in query. I asked it to place it in WHERE clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Dev (12/29/2011)
Place rand() * 100 function in your WHERE clause.
Example:
WHERE some_int_column= rand() * 100 -- or cast it as INT

That's not more or less random than ordering by NewID (probably less) and will still repeat rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

xRafo (12/29/2011)
how would you do that?,
maybe a good idea!

Create a table for values you've already seen (just the PK column) and then exclude those from the query.

Something roughly like this

SELECT TOP (1) name_emp

FROM Employees

WHERE <primary key column> NOT IN (select <column name> FROM #TableStoringValuesAlreadySeen)

ORDER BY NewID()

then insert that value into the table. Repeat 50 times. You can do a similar thing with using RAND in the where clause, however if you do that you need to account for the times when the query will return 0 rows (because the row that matched the RAND had already been returned)

Rough gist, but should get you on the right track.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 14

GilaMonster (12/29/2011)
Dev (12/29/2011)
Place rand() * 100 function in your WHERE clause.
Example:
WHERE some_int_column= rand() * 100 -- or cast it as INT
That's not more or less random than ordering by NewID (probably less) and will still repeat rows.

It will repeat but not toooo frequently. Also, it’s in WHERE clause so should be fast then ORDER BY. It's just a guess & needs testing that I can't do it right now.

In fact, I have a question on requirement itself. Does random mean not to repeat the value in 50 attempts? It is impractical requirement and we are trying to satisfy it.