Please help with random select

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

Please help with random select

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-08-22 : 01:56:00

I have a table
ID CODE NAME
611 GI08 Gian
610 GI07 Gian
618 GI15 Gian
620 FT Rope
320 ST Soler
345 ST01 Soler

I need the out put one random value from same name

like
ID CODE NAME
611 GI08 Gian (it should be random)
620 FT Rope
320 ST Soler (it should be random)

Please help!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-22 : 02:59:31

[code]SELECT ID , CODE , NAME
FROM (SELECT *, RANK() OVER(PARTITION BY NAME ORDER BY NEWID() ) RN FROM TableName ) T
WHERE RN=1[/code]

--
Chandu

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-08-22 : 03:00:54

Someone may have a smarter way of doing this but it worked on the test data,


create table junk
(
id int,
description char(20),
type char(20));

INSERT INTO junk VALUES (611,'GI08','Gian');
INSERT INTO junk VALUES (610,'GI07','Gian');
INSERT INTO junk VALUES (618,'GI15','Gian');
INSERT INTO junk VALUES (620,'FT','Rope');
INSERT INTO junk VALUES (320,'ST','Soler');
INSERT INTO junk VALUES (345,'ST01','Soler');

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY type ORDER BY newid()) AS rowNumber, *
FROM junk
) c2
WHERE rowNumber = 1

rowNumber	id	description	        type
1	        611	GI08                	Gian                
1	        620	FT                  	Rope                
1	        345	ST01                	Soler

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-08-22 : 03:02:12

quote:

Originally posted by erikhaselhofer

Bah, gonna leave it anyway!!!!


create table junk
(
id int,
description char(20),
type char(20));

INSERT INTO junk VALUES (611,'GI08','Gian');
INSERT INTO junk VALUES (610,'GI07','Gian');
INSERT INTO junk VALUES (618,'GI15','Gian');
INSERT INTO junk VALUES (620,'FT','Rope');
INSERT INTO junk VALUES (320,'ST','Soler');
INSERT INTO junk VALUES (345,'ST01','Soler');

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY type ORDER BY newid()) AS rowNumber, *
FROM junk
) c2
WHERE rowNumber = 1

rowNumber	id	description	        type
1	        611	GI08                	Gian                
1	        620	FT                  	Rope                
1	        345	ST01                	Soler