0

I have a list of records in TSQL query. Now I am in a certain situation where I want to select random 5 rows from output result.

Here is a snapshot of result-

enter image description here

Now if you see from image I have many rows coming according to this query that i've written-

declare @table1 table
    (
    cnt bigint,
    vid bigint
    )
    insert into @table1(cnt,vid) select count(video_id)as counts,video_views.video_id from video_views where video_views.is_active='True' group by video_views.video_id 
    select videos.*,vid from @table1 
    left join videos on videos.video_id=vid
    where cnt< '100' order by cnt desc

In query- I am managing a video site, Where I want to implement a section where videos with low views would come. Here in this query this table is Videos, Now whenever a video is viewed its view lists a row in table video_views. I have written this query in manner where only records those have views less than 100 would come by descending order.

As you can see my result shows me many records where views are less than 100. Now I wanted to select random 5 rows from this list.

How should I be approaching it to get it working?

Manoz
  • 6,507
  • 13
  • 68
  • 114

1 Answers1

1

The canonical way in SQL Server is to use order by newid():

select top 5 videos.*, vid
from @table1 left join
     videos
     on videos.video_id=vid
where cnt < 100
order by newid() desc ;

There are alternatives if performance is an issue.

Note: you should use single quotes only for string and date constants. Don't use single quotes for an integer constant such as 100.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786