How to get top 5 distinct

**r035198x** · Mar 19 '13, 05:44 AM

Use TOP http://msdn.microsoft.com/en-us/library/ms189463.aspx with descending order.

**Mudassir** · Mar 19 '13, 05:47 AM

yeah i did that but it repeats the data.. suppose a same video is commented twice, the in top5 in descending order, the same video id will be present twice.. :( any suggestions please ??

**r035198x** · Mar 19 '13, 05:53 AM

That should not happen. There is something wrong with your query. Post what you used.

**Mudassir** · Mar 19 '13, 05:59 AM

Code:

select distinct top 5 V.VideoID,V.VideoTitle,V.VideoPath,V.UploadedByName,
V.TotalViews,
case when
SC.URL is NULL then
	MC.URL+'/'+V.URL
ELSE
	SC.URL+'/'+V.URL end
as VideoURL from tblVideos V
Inner Join tblMainCategory MC  on (MC.CategoryID=V.CategoryID)
Left Outer Join tblSubCategory SC  on (SC.SubCategoryID=V.SubCategoryID)
where V.VideoID in (select top 5 VideoID from tblVideosComments where 
CommentStatus='A' order by CommentID desc)

now i am using this query but it brings 3 records when same video is commented twice ..

**r035198x** · Mar 19 '13, 06:04 AM

Start by just selecting the top 5 records and make sure that is working fine, then use that result as a subquery in an outer query that gets from other tables and columns.

**Mudassir** · Mar 19 '13, 06:06 AM

well i cant collect video ids from the comments table in descending order.. because the commentid is is the primary key of that table.. can you please post the query .. ??

**r035198x** · Mar 19 '13, 06:12 AM

You said "now i want to get top 5 latest videos ids ...".
If you want latest Ids then you just select top 5 of videoId and order by videoId descending.

Are you looking for something else? Like top 5 most commented videos instead?

**Mudassir** · Mar 19 '13, 06:13 AM

yeah exactly.. i want to get top 5 recently commented videos ..

**r035198x** · Mar 19 '13, 06:18 AM

That's different again! I said top 5 most commented and you have now just said top 5 recently commented! The two are different.

You need to be clear on what you're looking for when asking your questions so that you don't waste both your and other people's time.

If you are looking for most most commented then you need to use count; if you are looking for most recent then you need to order by some date. You can remove duplicates by selecting a MIN or MAX from the ties.

**Mudassir** · Mar 19 '13, 06:20 AM

well may b due to bad english i am unable to make my self clear.. well i want to get the top 5 recently commented videos. means those videos that are recently commented by users (n those videos can have even a single comment or 10000 comments) ... any sample for that .. ??

**r035198x** · Mar 19 '13, 06:26 AM

So for the comments try

Code:

SELECT  TOP (5)
        VideoID,
        MAX(CommentID)
FROM    tblVideosComments
GROUP   BY
        VideoID
ORDER   BY 
        MAX(CommentID) DESC;

If that's what you want then join the result of that with other tables to get the other data you want

**Mudassir** · Mar 19 '13, 06:29 AM

well thanks.. it worked perfectly..