pf » SQL to Select a random row from a database table

SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1

Here's a link to a method (requiring application logic) that has much better performance with large sets: http://www.greggdev.com/web/articles.php?id=6 The examples are PHP (ick), but they're easily understandable.

A simple SELECT * FROM table ORDER BY RAND() has to do a sort in memory- expensive. If you change the query to SELECT * FROM table WHERE RAND()>0.9 ORDER BY RAND() then you've reduced the overhead of the sort by 90%.

This will have the overhead of many RAND() statements, but on DB2 mainframes, it is a much faster technique.

2)Now proceed in selecting a random row from the database: SELECT FIRST 1 * FROM country ORDER BY rand()

using an integer will return that many rows, using a decimal will return that percent (ie .01 will bring back a 1 percent sample of rows)

Select cols From table SAMPLE RANDOMIZED ALLOCATION 10

Thanks Renu

SELECT * FROM table OFFSET RANDOM() LIMIT 1;

Col1 Col2 Col3 A 1 1(Sum of 1 and 0) B 2 3(Sum of 2 and 1) C 4 6(Sum of 4 and 2) D 5 9(Sum of 4 and 5)

Thanks in Advance

Delete Top 10 from [table_name]

set rowcount 10 delete from test_table

Please Suggest ... Thanks, Shantanu

I appreciate it!

Thank You Hill

I want to retrieve some fields like customer name,customer_number and the totals of the sold_qty,billed_fees per annum for different customers in a single row per customer. I am getting group by expression error when I try can anyone provide some updates?

In a small dataset I find that the same record as that just selected randomly can be randomly selected again.

How would you modify the random query (for MS SQL Server) to NOT INCLUDE the previously selected record on a new request.

In my use we randomly display a new image (redord) on refreshing the web-browser page (which causes the query to run.)

Thank you.

thanks in advance

Each time you run the script you will get the same set of rows.

truncate table tablename --10

Note: the --10 is an option specifying the row limit. You can then perform your select or delete statement after that and the results will be truncated at 10 rows (or however many you specify). For example:

truncate table customers --10

select * from customers

This will return the first 10 customers. Enjoy!

This is Ravi, need to know how to fetch the random rows in sybase.

Thanks

Ravi

I have a table 2. name of the table is constant. ex:CustomFields 3. but the columns names & no of colmuns differ. 4. as this table contains millions of rows, it is difficult to browse which column has atleast one value in its respective rows. 5. so find the columns which have atleast one value in it. 6. two sets of results i want: a) cols with atleast one value b) cols with out any value.

The TRUNCATE command will *delete* your data.

The "-- 10" command is a SQL comment and will have no effect.

Pete, I suggest you delete/mark this dangerous post.

I want to update one table in sybase but it is taking very long time as the table contains millions of records. The server is going down. Is there any way to do batch wise

sorry for my bad english.

I have a table with 2 colums, and I wanted to get all the records in a single row (My SQL). Can any one help me on this.

testname testvalue -------- --------- AC 21 PC 12

i want the table selection like the following

AC PC 21 12

Please give me solution..

thanks in advance..

my email:selvinm@gmail.com

'Get the number of possible rows in the table varcount = run "Select count(rowid) from table"

'then, in code, generate a random number between 0 to the rowcount varOFFSET = rnd * varcount

'Then run your new select statement offsetting it by the random value randomrow = run "SELECT column FROM table LIMIT 1 OFFSET " & varOFFSET

I tested it and it improved performance greatly. I only have 5000 records and it helped a lot. I imagine with millions of records this would be much more valuable.

It's been a while! Looked up this post because I couldn't remember the DB2 syntax I contributed earlier -- come to find out, it doesn't work on DB2 v8.2 running on z/OS. I get an exception saying the RAND() function is non-deterministic, and as such, can't be used in an ORDER BY CLAUSE...

The work-around:

SELECT <column_name>, RAND() as IDX FROM <table_name> ORDER BY IDX FETCH FIRST 1 ROWS ONLY

SELECT * FROM people ORDER BY RND(personID)

It's important you use a number column

I have a table in which I have result, defect,name as columns:

Name Result Defect A P x A P x1 B P X2

When defect is not null and defect!=' ', I select the rest of the defect for a particular 'name'='A'

I get the below result

x x1

i.e two rows, but I need this in a single row like:

x,x1

is this possible?

I tried with COALESCE function in T-SQL... here it goes below:

I need a confirmation whether its correct or not... cos I dont have sufficient permission to create this function in my DB...

CREATE FUNCTION dbo.UDF_getdefect ( @name varchar(1000) ) RETURNS varchar(1000) AS BEGIN DECLARE @defect varchar(1000), @Delimiter char SET @Delimiter = '+' SELECT @defected = COALESCE(@defected + @Delimiter, ') + defect FROM (Select defect from table1 where defect is NOT NULL and defect != ' ' and name =@name) derived where defect is NOT NULL and defectReportId != ' ' and name =@name RETURN ( SELECT REPLACE(@defected,')+(',') + (') AS [Defects]) END

If I need the result of this function in another Select query can I use it as below?

(Select getDefect(name),name from table1) as defected

I have 3 laks recored in table t1.I want to retin first 100 recored and rest of records should be delted....can any one hlep..

ADVACED THANKS

id should be an autonumber field

Another great solution to selecting a random row: count number of rows in the table, pick a random value from 0 to that count (using applic logic to do random), then fetch data on that row using "LIMIT ROW#,1". This is the only method that works flawlessly here, across hundreds of millions of rows, in production, on mysql 5.0 in a myisam table in under 100msec. The trick is to use the same WHERE clause on both the count and the pull.

1 Rahul Bose Male #13, Bangalore, 686691 Landphone-4452121252 1 Rahul Bose Male #13, Bangalore, 686691 Mobilephone2-558521252 1 Rahul Bose Male #13, Bangalore, 686691 Official_Email-TL@abc.com

Q.(2)How I create auto increment in oracle like My Sql. Q.(3) How I edit data in PL/SQL

I am kartik. i want to study material. help me. All SQL Study Material. plz All Information in my mail address send to you.

Thank You Kartik Shah