1

I've got a tricky (at least for me it's tricky) question, I want to arrange data by comment count. My first table is called all_comments which has these columns (more but not essential):

comment, target_id

My second table is called our_videos which has these columns (more but not essential):

id, title

I want to get the count of all comments that have target_id same as id on 2nd table and arrange that data by comment count. Here is example of what I want:

TABLE #1:

id    target_id
----------------
1        3
2        5
3        5
4        3
5        3

TABLE #2:

id   title
-----------
1    "test"
2    "another-test"
3    "testing"
5    "......"

This is basically saying that data, that is in 2nd database and have id of 3 have 3 comments, and data that have id of 5 have 2 comments, and I want to arrange that data by this comment count and get result like this:

RESULT:

id    title
----------------
3     "testing"
5     "......."
1     "test"
2     "another-test"

If I missed any important info needed for this question just ask, thanks for help, peace :)

Improve this question
8
  • use join and order statements in your query Commented Feb 9, 2015 at 20:08
  • Yeah I know that, but it's pretty confusing, tried to work out a way with them but didn't managed. Commented Feb 9, 2015 at 20:09
  • @dnc123 - so try something ... it's a basic SELECT statement ... you can't hurt anything with a a SELECT .. so look at the individual parts of the SELECT .. and put it together. You have 1) SELECT ... 2) FROM ... and 3) WHERE .... Commented Feb 9, 2015 at 20:10
  • There are a bunch of examples of this on the web. You will need to utilize a "SQL Join" (Probably an inner join) and a "SQL Group By" with an Aggregate such as Count(*). Google "SQL Join" First and look at some of the examples. Then when you figure out the Join Google "SQL Group By" Commented Feb 9, 2015 at 20:13
  • If it is hard to do it all as one big query, you can try to do it step by step. Create a view (or CTE if you are using SQL Server) that gives something that is easier to work with. And then use that view to define another view, and finally use your latest view (or several) to create the actual query. Commented Feb 9, 2015 at 20:15

3 Answers 3

Reset to default
2

it is very simple query and you definitely have to look at any sql tutorial

naive variant will be:

select videos.id, videos.title, count(*) as comment_count
from videos
left outer join
comments
on (videos.id = comments.target_id)
group by videos.id, videos.title
order by comment_count desc

this version has some performance problems, because you have to group by name, to speed up it we usually do next thing:

select videos.id, videos.title, q.cnt as comment_count
from videos
left outer join
(
    select target_id, count(*)
    from comments
    group by target_id
) as q
on videos.id = q.target_id
order by q.cnt DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Just using max(title) on the first query would eliminate the grouping on that column. And of course you need to alias the inner count column.
@shawnt00 it will work, but: 1. it will introduce additional string comparison 2. it looks weird - what is the sense of max title from users point of view?
It's a common approach to flattening a column aggregate when you know only a single value is coming back. I think it's much better than adding a bunch of group by's. Not sure what you mean by string comparison though.
I see you meant string comparison inside the aggregate. I think that's an insignificant consideration for the most part. Obviously you have to choose (stylisticly) between writing a derived table/inline view vs my modification. I honestly don't know which is easier for a novice programmer to understand.
The overhead of processing the extra grouping column and/or the optimizer determining that is doesn't need it is probably much higher than the string comparison anyway. Older systems don't allow nesting of queries so it's good to know both options.
|
0 
select videos.id, videos.title, isnull(cnt, 0) as cnt
from videos
left outer join 
    (select target_id, count(*) as cnt
    from comments
    group by target_id) as cnts
on videos.id = cnts.target_Id
order by isnull(cnt, 0) desc, videos.title
Improve this answer

Comments

0

Some systems will let you write this even though sorting is not strictly supposed to happen on an column not included in the output. I don't necessarily recommend it but I might argue it's the most straightforward.

select id, title from videos
order by (select count(*) from comments where target_id = videos.id) desc, title

If you don't mind having it in the output it's a quick change:

select id, title from videos,
    (select count(*) from comments where target_id = videos.id) as comment_count
order by comment_count desc, title

SQL generally has a lot of options.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.