Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to return top 100 rows by a column value and then randomize theese top 100 rows?

I'm using MS SQL and i've managed to create a query that selects top 100 rows and randomize them like this

SELECT TOP 100 * FROM Inlagg  ORDER BY NEWID() 

I've also managed to create a query that returns the top 100 rows according to the column likes like this

SELECT TOP 100 * FROM Inlagg  ORDER BY Likes DESC

My question is now, how can i target theese top 100 rows by Likes and then randomize theese top 100 values?

Any help or input highly appreciated, thanks!

like image 361
Mattias Avatar asked Apr 22 '15 12:04

Mattias


People also ask

How do I SELECT random 100 rows in SQL?

The function RAND() generates a random value for each row in the table. The ORDER BY clause sorts all rows in the table by the random number generated by the RAND() function. The LIMIT clause picks the first row in the result set sorted randomly.

How do I randomly SELECT rows from a table?

To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).


2 Answers

You can use something like

SELECT *
FROM (SELECT TOP 100 * FROM Inlagg  ORDER BY Likes DESC) as T
ORDER BY NEWID()

or (for those who prefers common table expressions not subqueries)

WITH CTE_TOP as (SELECT TOP 100 * FROM Inlagg  ORDER BY Likes DESC)
SELECT * FROM CTE_TOP ORDER BY NEWID();
like image 134
Andrey Korneyev Avatar answered Oct 21 '22 04:10

Andrey Korneyev


may be this also works

 select * 
    from Inlagg   t1
    inner join
    (
      select distinct top 100 Likes
      from Inlagg  
      order by Likes
    ) t2
      on t1.Inlaggid = t2.Inlaggid

Guys i'm sorry to say that i'm unable to send comments may be java Api is not supporting my browser.Why it wont works it will give top 100 records based on the order by combination.Coming to the performance issue may be this table column will have clustered or non clustered index will be there.Scan lookups will be reduced i just said it is another way not the exact solution

like image 44
mohan111 Avatar answered Oct 21 '22 06:10

mohan111