choose a random record

Question

Post reply

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: 1004494 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

xRafo

SSChasing Mays

Points: 623

December 29, 2011 at 9:26 am

#1426521

Thanks!

if there is a more efficient would it be?

____________________________________________________________________________
Rafo*

Gail Shaw SSC Guru Points: 1004494 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: 245813 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()

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

xRafo SSChasing Mays Points: 623 More actions · Answer 6

xRafo

SSChasing Mays

Points: 623

December 29, 2011 at 9:43 am

#1426541

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: 245813 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 ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

xRafo SSChasing Mays Points: 623 More actions · Answer 8

xRafo

SSChasing Mays

Points: 623

December 29, 2011 at 9:49 am

#1426544

how would you do that?,

maybe a good idea!

____________________________________________________________________________
Rafo*

Devendrakumar Shirbad 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

xRafo

SSChasing Mays

Points: 623

December 29, 2011 at 9:56 am

#1426552

Hi,

thanks!

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

____________________________________________________________________________
Rafo*

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

Devendrakumar Shirbad

SSC-Forever

Points: 42493

December 29, 2011 at 10:06 am

#1426559

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: 1004494 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: 1004494 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 Shirbad 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.