CODEWITHSUNDEEP
sql-serverdistinct
maliks
maliks

Reputation: 1112

Select numbered Distinct values from Sql Server column

I have a Sql Server query as: 

SELECT  AC.Author_ID, A.Author_Name, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID, AC.Year
FROM    AuthorCoAuthor  AC
JOIN    AuthorPaper     AP ON AP.Author_ID  = AC.Author_ID AND 
                        AP.Paper_ID = AC.Paper_ID
JOIN    Author          A  ON A.Author_ID   = AC.Author_ID
ORDER BY AC.Author_ID, AC.Year, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID

which returns almost 221317 rows. The sample output is: 

Author_ID | Author_Name | Paper_ID | CoAuthor_ID | Venue_ID | Year  
------------------------------------------------------------------
677       | Nuno Vasc   | 812229   | 901706      | 64309    | 2005
677       | Nuno Vasc   | 812486   | 901706      | 65182    | 2005
677       | Nuno Vasc   | 818273   | 901706      | 185787   | 2007  
1359      | Peng Sui    | 818373   | 457348      | 18534    | 2005  
1359      | Peng Sui    | 868273   | 542321      | 184222   | 2006  
...       | ...         | ...      | ...         | ...      | ...

I want to have only 10 distinct Author_ID to be returned by the query, whereas I tried by adding 

WHERE  COUNT(DISTINCT(AC.Author_ID)) <= 10

this WHERE clause into the same query but still I'm getting same number of rows i.e. all the data which normally query returns.

Why my WHERE clause not working here?

Upvotes: 0

Views: 49

Answers (1)

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

To get 10 Distinct Author_ID, you can use GROUP BY with TOP 10. Then join the result (10 rows here) with other tables to show columns you want, in the select list.

You can try this:

SELECT AC.Author_ID, A.Author_Name, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID, AC.Year
FROM
(
   SELECT TOP (10) AC.Author_ID
   FROM AuthorCoAuthor AC
   GROUP BY AC.Author_ID
   ORDER BY AC.Author_ID
) DS
JOIN AuthorCoAuthor  AC ON AC.Author_ID = DS.Author_ID
JOIN    AuthorPaper     AP ON AP.Author_ID  = AC.Author_ID AND 
                    AP.Paper_ID = AC.Paper_ID
JOIN    Author          A  ON A.Author_ID   = AC.Author_ID
ORDER BY AC.Author_ID, AC.Year, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID

UPDATE

By the Previous Answer, You can get less than 10 Distinct Author if both AuthorPaper & Author table doesn't have one of those Top 10 Sequential Author. For that reason, you need to check those condition when you get 10 Distinct Author so that it always give you 10 Distinct Author if and only if AuthorCoAuthor has at least 10 Distinct Author. So, To make sure, you always get 10 Distinct Author, you can use this one: 

SELECT AC.Author_ID, A.Author_Name, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID, AC.Year
FROM
(
   SELECT TOP (10) AC.Author_ID
   FROM AuthorCoAuthor AC
   JOIN AuthorPaper AP 
      ON AP.Author_ID  = AC.Author_ID 
         AND AP.Paper_ID = AC.Paper_ID
   JOIN Author A  ON A.Author_ID = AC.Author_ID
   GROUP BY AC.Author_ID
   ORDER BY AC.Author_ID
) DS
JOIN AuthorCoAuthor  AC ON AC.Author_ID = DS.Author_ID
JOIN AuthorPaper AP 
   ON AP.Author_ID  = AC.Author_ID 
      AND AP.Paper_ID = AC.Paper_ID
JOIN Author A  ON A.Author_ID = AC.Author_ID
ORDER BY AC.Author_ID, AC.Year, AC.Paper_ID, AC.CoAuthor_ID, AP.Venue_ID

Upvotes: 1

Related Questions