Display 1 random row (when >1 rows pass selection)

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)

Display 1 random row (when >1 rows pass selection)

Author

Topic

Jawad
Starting Member

17 Posts

Posted - 2011-12-12 : 08:37:46

Hi

I required to show 1 row randomly among all rows that pass selection criteria every time user pass query.



create table T1
(col_1 smallint,
Col_2 varchar(10)
);

insert into T1 values (1,'abc');
insert into T1 values (1,'pqr');
insert into T1 values (1,'xyz');
insert into T1 values (2,'abc');
insert into T1 values (3,'abc');
insert into T1 values (4,'abc');
insert into T1 values (5,'abc');


select * from T1
where col_1=1

Now above query return 3 rows I wish to show 1 row randomly in return.
Note : Need Random every time not the min/max row.

Wishes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-12 : 08:41:40

[code]
select top 1 * from T1 order by newid()
[/code]

KH
[spoiler]Time is always against us[/spoiler]

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-12 : 09:02:32

To improve the performance try below code


select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:39:49

quote:
Originally posted by johntech

To improve the performance try below code

select * from [yourtable] where [yourPk] in
(select top 10 percent [yourPk] from [yourtable] order by newid())

can you explain how this will improve performance over Tans suggestion above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jawad
Starting Member

17 Posts

Posted - 2011-12-13 : 05:13:01

thanks khtan

that a very quick and simple solution

select top 1 * from T1
where col_1=1
order by newid()

Wishes

Jawad
Starting Member

17 Posts

Posted - 2011-12-20 : 12:15:23

thanks everyone who reply me.
Sorry infect I fail to communicated my requirement 1st time.
I wish to get random min record per value of col_1 in case of draw.


drop table T1;

create table T1
(col_1 smallint,
Col_2 varchar(10),
Col_3 int,
);

insert into T1 values (1,'abc',10);
insert into T1 values (1,'pqr',10);
insert into T1 values (1,'xyz',12);
insert into T1 values (2,'abc',17);
insert into T1 values (2,'xyz',15);
insert into T1 values (2,'pqr',15);
insert into T1 values (3,'abc',14);
insert into T1 values (3,'pqr',16);
insert into T1 values (3,'XYZ',14);

select * from T1;

Out put


col_1   Col_2   Col_3
1	abc	10
1	pqr	10
1	xyz	12
2	abc	17
2	xyz	15
2	pqr	15
3	abc	14
3	pqr	16
3	XYZ	14

I need single min(col_3) value against every distinct value of Col_1.
If for single value of Col_1 there are multiple values min(col_3), then they show randomly.
as under


col_1   Col_2   Col_3
1	abc	10
2	pqr	15
3	abc	14

Next execution will be like


col_1   Col_2   Col_3
1	pqr	10
2	pqr	15
3	XYZ	14

Wishes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 12:24:13

[code]
SELECT t1.col_1, t2.Col_2, t3.Col_3
FROM (SELECT DISTINCT col_1 FROM T1) t1
CROSS APPLY (SELECT TOP 1 Col_2,Col_3
FROM T1
WHERE col_1 = t1.col_1
ORDER BY Col_3 ASC,NEWID()
)t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources