Find Top 5 rows - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Find Top 5 rows

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-10-13 : 11:45:47

Hi,

My table structure looks like this:

Articles
ArticleID
Title
Views
CategoryID

Categories
CategoryID
Title

I am writing a query to select top 5 random articles; given a
category

DECLARE @rwCnt int
SET @rwCnt = 5 

SELECT 
    A.ArticleId,
    A.Title
FROM Categories AC (NoLock)
CROSS APPLY (
   SELECT TOP(@rwCnt) ArticleId, Title
    FROM Articles A (NoLock)
    WHERE A.CategoryId = AC.CategoryId
	and A.CategoryId = 50
    ORDER BY CHECKSUM(NEWID()) 
) A

Is there a better way to write this query with best performance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:48:37

No need for checksum.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:50:49

[code]DECLARE @rwCnt int
SET @rwCnt = 5

SELECT top (@rwCnt)
ArticleId,
Title
FROM articles
where dategoryid = 50
order by NEWID()[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:55:18

[code]
CREATE PROC GetTopArticles
@Title varchar(100),
@rwCnt int

SELECT CatTitle,
ArticleTitle,
Views
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.CtegoryID ORDER BY NEWID()) AS Seq,a.Title AS ArticleTitle,a.Views,c.Title AS CatTitle
FROM Artciles a
INNER JOIN Categories c
ON c.CategoryID=a.CategoryID
)t
WHERE t.Seq <=@rwCnt
AND t.Title=@Title[/code]

lols
Posting Yak Master

174 Posts

Posted - 2008-10-13 : 12:07:32

Which one's query is better in performance Visakh or Peso? I do not have SSMS to test the results :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:16:06

I bet $0.05 my suggestion will perform faster and use less resources.

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 14:22:48