Get random records..how ? please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get random records..how ? please

Author

Topic

a4nsd
Starting Member

20 Posts

Posted - 2007-02-10 : 15:26:09

Hi every one.How can I get random 4 records in my table.
Example I want to get top 4 random records
Select top 4 from tbl_Products.....?
Thanks in appreciate

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-10 : 15:43:19

Select top 4 cola,colb,,,coln from tbl_Products

Will get you 4 random records, since you did not provide an order by clause.

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-10 : 16:30:09

Does the TOP operator default to clustered index (if present) when not supplying ORDER BY?

SELECT TOP 4 * FROM tbl_Products
ORDER BY NEWID()

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-10 : 16:38:37

>> Does the TOP operator default to clustered index (if present) when not supplying ORDER BY?

It is not documented that top N always return the N first records of the clustered index in their clustering order.
Still it does not matter, it is still 4 random records.

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-10 : 16:50:11

Ok. Needed an update on that.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-10 : 17:27:46

Cool.

And to the poster, there is no logical order imposed in a relational database.
The only order is the one you specify in the order by clause.

Here peso specified a random order in that clause.

For large tables order by newid() can be taxing.
In which case I often resort to:

select top 4
(select top X ... from table order by colX desc) X
order by .. newid()

where top X gives a large enough sample, usually ordered by the most "recent" records desc.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-10 : 18:29:14

Using newid() on the full row will probably cause a work table of the full table to be created. It is faster to use the PK or any non clustered unique index

select * from tbl where col in (select col from tbl order by newid())

select t.*
from tbl t
join (select col1, col2 from tbl order by newid()) t2
on t.col1 = t2.col1
and t.col2 = t2.col2

Same applies to Oracle - haven't tried other databases.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-10 : 20:40:33

If your table is fairly large and has a sequentially increasing identity primary key with few missing values in the range of Min PK to Max PK, something like this may be faster.

It randomly generates 100 numbers in the range of Min PK to Max PK, selects them from the table, and selects the top 4. With a large table, this may be much faster, because it should only look up 100 rows in the table by PK, instead of scanning the whole table.


select	top 4
	*
from
	MyTable a
where
	a.PKID in
	(
	select	top 100 percent
		-- Select random numbers in the
		-- range of min PK and max PK
		PKID = (aaa.NUMBER%(bbb.max_PK-bbb.min_PK+1))+bbb.min_PK
	from
		(
		select	top 100 percent
			-- Generate random numbers
			NUMBER = abs(convert(bigint,convert(varbinary(20),newid())))
		from
			-- Function available here:
			-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
			F_TABLE_NUMBER_RANGE(1,100) aaaa
		) aaa
		cross join
		(
		select
			-- Get min and max Primary Key ID from table
			min_PK = min(PKID),
			max_PK = max(PKID)
		from
			MyTable bbbb
		) bbb
	)
order by
	newid()

CODO ERGO SUM

a4nsd
Starting Member

20 Posts

Posted - 2007-02-11 : 01:13:42

Thanks all.That is what i need.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 14:25:18

I'm posting a revision to what I posted earlier, because it appears less likely to result in a table scan, and more likely to use a nested loop lookup.


declare @id table (ID int not null primary key clustered )

insert into @id
select	distinct top 100 percent
	-- Select random numbers in the
	-- range of min PK and max PK
	ID = (aaa.NUMBER%(bbb.Max_PK-bbb.Min_PK+1))+bbb.Min_PK
from
	(
	select	top 100 percent
		-- Generate random numbers
		NUMBER = abs(convert(bigint,convert(varbinary(20),newid())))
	from
		-- Function available here:
		-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
		F_TABLE_NUMBER_RANGE(1,100) aaaa
	) aaa
	cross join
	(
	select
		-- Get min and max Primary Key ID from table
		Min_PK = min(bbbb.PKID),
		Max_PK = max(bbbb.PKID)
	from
		MyTable bbbb
	) bbb
order by
	1


select	top 4
	a.*
from
	@id x
	left join
	MyTable a
	on ( a.PKID = x.ID )
where
	a.PKID is not null
order by
	newid()