extract data from table randomly

[Emi]

Hi every body I need a small help if possible>>>now I used this query to retrive random records from a table...SELECT field_names FROM table_name WHERE some_conditions ORDER BY NEWID();the problem is that some records that are retrived are appeared more than once Could you help me in this problem??????

[vijay]

Hi..SELECT * FROM tbl_name ORDER BY RAND( ) LIMIT 5 from the above qry u will have any random 5 records from the specified table name..I think suppose this thing you want.. isn't it..?Regards,Vijay

[Emi]

Thanx..but this query SELECT * FROM tbl_name ORDER BY RAND( ) LIMIT 5 didn't work with me.. the probleam is in syntaxi use MS SQL SERVER 2005 EXPRESSplease help me on this problem

[nibe49]

Hi EmiNewid() is specific to SQL Server.It's ability to sort in a (pseudo) random manner as you have used it is known about, but not mentioned in BOL or any other official docs I can find. It was probably not intended for use that way.Random means ... random. With a small table you will get the same selections returned frequently because there are only a small number of choices, with a bigger table you may not get the same selection returned for a long time, maybe even longer than the age of the universe, but then again you may get 2 consecutive result sets the same.In any random method where you want each subsequent set to be unique you have to compare the present set with all previous sets and discard it if it has appeared before. This can't be done in a single query, it involves - for each selectionthe initial selectcomparison with previous selections (another select)an insert into the previous selection if this is a new set of values.It's not essential but I would recommend that you separate the random generation to work purely on a unique key ( but not a uniqueidentifier) rather than using newid().Hope this helpsNibe

[Yahweh]

Thanx..but this query SELECT * FROM tbl_name ORDER BY RAND( ) LIMIT 5 didn't work with me.. the probleam is in syntaxi use MS SQL SERVER 2005 EXPRESSplease help me on this problem

Rand() and Limit 5 are specific to MySQL.The equivalent query in SQL server looks like this:

SELECT TOP 5 * FROM table ORDER BY NewID()

That method isn't very efficient if you have 100s of 1000s of records, because it will have to create a NewID() for each record before returning the top 5.To return a single record, there's a little more efficient way:- Get the maximum uniqueID in your table, store that information in a variable called MaximumID- Multiply MaximumID * rnd(), store that information in a variable called RandomID- Select the first record with an ID >= RandomIDIn T-SQL:

declare @MaximumID int = Select Max(ID) from table1declare @RandomID int = Rand() * MaximumIDSelect Top 1 * From table1 where ID >= MaximumID

Or, you can always do what Microsoft suggests and create a table with random columns already in the table definition.