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

Thank you in advance

you can't just demand that the OP or whoever reads this blog is going to do this specifically for you and then email it to you. "thanks in advance" doesn't do anything, either.

its also very sad to see the same question with the numbers changed posted a million times - also with the answer posted about a dozen times. everyone should just atctually READ and tried what was already presented here instead of asking the same thing one more time...

BUUUT - i do know some thing who can answer ALL of YOUR questions: google.com

select * from (select rownum rowix, i.* from my_table order by dbms_random.random) where rowix < 2

Some one give me this: $random = Rand() $sql = "select * from wallpaper where id=".$random." limit 3";

But its not work please optmize it for me

Thanks

Thanks

Matheus Garcia wrote: Solution for MSAccess: select top 1 column from tabela order by RND(INT(NOW*id)-NOW*id) "id" is the column auto_numeric primary key ;D

So for 100 random rows change the TOP 1 to TOP 100

how to get row count in ms sql server with that i can keep for loop in my program...

I was trying for the same logic since from 3 days. and now i got it.

really good one

Is is possible to use column id in select statement instead of column names. "select columnid from table order by 1" like the order by 1 refer to first column.

A comparison of different approaches that can be found in the following post, shows that the ORDER BY RAND() is the worst possible option in MySQL: http://sillybits.wordpress.com/2009/08/24/fetching-random-rows-from-a-database-table/

Now I have another form packcheck.In this form also contains 4 textbox.Now i entered datas to all the textbox.

now i click the submit button it check the database. the datas are already present in the database then entered datas are updated else it display the message "the entered datas are not present in the database. (use the code aspx and c#).

this is my questio,please help me.

advance thanks. Please help me,it is very urgent.please mail to me.

For your help.

PD: Your little article help to management some data in Chile, South America

I have a question in here. (IN SQL) How to add and extra row at the top of a "select query request". For example: If I wish to have the headings in the row 1 of my select Procedure, How can I achieve that.

Thanks In Advance. Pallav Vidwans

SELECT field FROM table ORDER BY RAND( ) ) ORDER BY RAND( ) LIMIT 15 , 60

Thanks a lot - it worked for me and I am sure that it will be appreciated.

Thanks once again, Piyush

Author: Matheus Garcia}

Many thanks for this post.

Greetings Nebu

using between operator