473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to get top 5 distinct

85 64KB
i have two tables 1.table of videos which looks like
VideoID int
VideoTitle varchar(max)
Videodescripton etc...

and second table Comments which has
CommentID
VideoID
Comments etc..
now i want to get top 5 latest videos ids from the comments table how i will do that.. by using distinct is not the solution..
Mar 19 '13 #1

✓ answered by r035198x

So for the comments try

Expand|Select|Wrap|Line Numbers
  1. SELECT  TOP (5)
  2.         VideoID,
  3.         MAX(CommentID)
  4. FROM    tblVideosComments
  5. GROUP   BY
  6.         VideoID
  7. ORDER   BY 
  8.         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

12 6120
r035198x
13,262 8TB
Use TOP http://msdn.microsoft.com/en-us/library/ms189463.aspx with descending order.
Mar 19 '13 #2
Mudassir
85 64KB
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 ??
Mar 19 '13 #3
r035198x
13,262 8TB
That should not happen. There is something wrong with your query. Post what you used.
Mar 19 '13 #4
Mudassir
85 64KB
Expand|Select|Wrap|Line Numbers
  1. select distinct top 5 V.VideoID,V.VideoTitle,V.VideoPath,V.UploadedByName,
  2. V.TotalViews,
  3. case when
  4. SC.URL is NULL then
  5.     MC.URL+'/'+V.URL
  6. ELSE
  7.     SC.URL+'/'+V.URL end
  8. as VideoURL from tblVideos V
  9. Inner Join tblMainCategory MC  on (MC.CategoryID=V.CategoryID)
  10. Left Outer Join tblSubCategory SC  on (SC.SubCategoryID=V.SubCategoryID)
  11. where V.VideoID in (select top 5 VideoID from tblVideosComments where 
  12. CommentStatus='A' order by CommentID desc)
  13.  
  14.  
now i am using this query but it brings 3 records when same video is commented twice ..
Mar 19 '13 #5
r035198x
13,262 8TB
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.
Mar 19 '13 #6
Mudassir
85 64KB
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 .. ??
Mar 19 '13 #7
r035198x
13,262 8TB
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?
Mar 19 '13 #8
Mudassir
85 64KB
yeah exactly.. i want to get top 5 recently commented videos ..
Mar 19 '13 #9
r035198x
13,262 8TB
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.
Mar 19 '13 #10
Mudassir
85 64KB
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 .. ??
Mar 19 '13 #11
r035198x
13,262 8TB
So for the comments try

Expand|Select|Wrap|Line Numbers
  1. SELECT  TOP (5)
  2.         VideoID,
  3.         MAX(CommentID)
  4. FROM    tblVideosComments
  5. GROUP   BY
  6.         VideoID
  7. ORDER   BY 
  8.         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
Mar 19 '13 #12
Mudassir
85 64KB
well thanks.. it worked perfectly..
Mar 19 '13 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: ben | last post by:
This is a PHP / MySQL kind of question. I am making a script which simply pulls information from a database and displays it on screen. BUT there will be entries where fields could be the same, and...
5
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the...
2
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an...
4
by: Johnson, Shaunn | last post by:
Howdy: Can someone tell what the difference (and why you would use it) is between the following: select distinct on (col_1, col_2), col_1, col_2, col_3
18
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are...
1
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT...
3
by: orekinbck | last post by:
Hi There Our test database has duplicate data: COMPANYID COMPANYNAME 1 Grupple Group 2 Grupple Group 5 Grupple Group 3 Yada Inc 4 Yada...
6
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214,...
4
by: monomaniac21 | last post by:
hi! is it possible to do the aforementioned query - selecting only distinct in 1 col but retrieving all other cols at the same time. regards marc
2
by: Techhead | last post by:
I need to run a SELECT DISTINCT query across multiple fields, but I need to add another field that is NON-DISTINCT to my record set. Here is my query: SELECT DISTINCT lastname, firstname,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.