Random selection of data - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Random selection of data

Author

Topic

sneha24
Starting Member

4 Posts

Posted - 2011-04-13 : 08:14:39

I have a table called Tbl_Transactions.it has fields Transaction_id,Employee_name, Customer_number, Customer_name.
Transaction_id is unique.
I would like to select 10 records of each employee in Random from the Transaction table.
Any help is really appriciated.
Thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 08:25:30

What is Type of Transaction_ID ?

Vaibhav T

If I cant go back, I want to go fast...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-13 : 08:28:30

You can do it using row_number function like this:


WITH cte AS 
(
SELECT 
   ROW_NUMBER() OVER (PARTITION BY Employee_Name ORDER BY (NEWID())) AS rowNum,
   *
FROM
   Tbl_Transactions
)
SELECT * FROM cte WHERE rowNum <= 10

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 08:39:26

Perfect

Vaibhav T

If I cant go back, I want to go fast...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-13 : 08:40:21

Just be aware that performance will be poor on larger tables.

--
Gail Shaw
SQL Server MVP

sneha24
Starting Member

4 Posts

Posted - 2011-04-13 : 09:27:16

Thank you. Thats Wonderful.

quote:
Originally posted by sunitabeck

You can do it using row_number function like this:

WITH cte AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Employee_Name ORDER BY (NEWID())) AS rowNum,
*
FROM
Tbl_Transactions
)
SELECT * FROM cte WHERE rowNum <= 10

Sneha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources