getting top 5 records using union and subqueries

Question

1.00/5 (1 vote)

See more:

To get top 3 commented as well as 2 Liked blogs (total 5 records), i am using this query

SQL

select Top 3* from (select Top 10000 NEWID() nid,blogid,COUNT(*) counts from Blog_Comment where isAdmin=0 and isActive=1    group by BlogId order by counts desc ) b
 union
select top 2 * from (select  Top 10000 NEWID() nid,BlogId,SUM(Likecount) counts from Blog_Like where PartnerId not like 'MS%'  group by BlogId order by counts desc ) a  order by nid

working nice.. but getting duplicate blogids for suppose..

nid	                               blogid	counts
E3128722-1C3C-4DFF-B099-080FA587562E	129	11
C6C55763-F395-4332-8E39-5443E3D81DA2	129	2
58072BB7-B957-4725-A3AB-6529A4931FB4	141	7
8B46BC4A-6A41-46B8-ACCC-8722D5713D46	124	2
7CE73B0C-573C-43A4-9620-F452E6D5435B	140	7

how can i get here unique blogids...Please help
Thanks

Posted 3-Jan-12 0:33am

krishnaMurali

Updated 3-Jan-12 5:20am

Mehdi Gholam

v2

Add a Solution

Comments

Amir Mahfoozi 3-Jan-12 6:44am

Test each query separately and tell us which one is not working well. It's not clear which of them is not working well.

Sergey Alexandrovich Kryukov 3-Jan-12 12:43pm

The only problem I can see is: you don't define what is "top". Databases do not assume any guaranteed predefined order.
--SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-01-03T08:52:00

If I'm interpreting you SQL correctly:
- you have two separate sql statements
- first one returns 3 rows
- second one returns 2 rows
- both queries are independent (based on SQL syntax)
- the results from both queries are combined by union.

So this means that there is no condition in the first query that would exclude the blog id from the second query or vice versa. This means that the solution is data specific.

Now without knowing the relations between those tables, I'm just guessing but perhaps you could modify the query to something like:

SQL

select Top 3 *
from (select blogid,
             COUNT(*) counts
      from Blog_Comment
      where isAdmin=0
      and isActive=1
      group by BlogId
      order by counts desc ) b
union
select top 2 *
from (select  BlogId,
              SUM(Likecount) counts
      from Blog_Like
      where PartnerId not like 'MS%'
      and BlogId not in (select top 3
                                blogid,
                                COUNT(*) counts
                         from Blog_Comment
                         where isAdmin=0
                         and isActive=1
                         group by BlogId
                         order by counts desc)
      group by BlogId
      order by counts desc ) a  order by counts

Most likely the query could by simplified a lot but that would require some knowledge about the data model.