CODEWITHSUNDEEP
sql-serverdatabase
Mattias
Mattias

Reputation: 3169

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!

Upvotes: 6

Views: 9060

Answers (2)

mohan111
mohan111

Reputation: 8865

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

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

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();

Upvotes: 9

Related Questions