Select numbered Distinct values from Sql Server column

Question

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?

Sorry ... on my phone and don't have my glasses. Sucks getting old — John Cappelletti
– John Cappelletti, Commented Jun 18, 2016 at 19:59

Mahedi Sabuj · Accepted Answer · 2016-06-18 20:25:31Z

1

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

edited Jun 18, 2016 at 20:25

answered Jun 18, 2016 at 19:43

Mahedi Sabuj

2,9543 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maliks Over a year ago

Not allowing me to use DISTINCT keyword with TOP(10) here

Mahedi Sabuj Over a year ago

Updated the Answer. Check Now.

Collectives™ on Stack Overflow

Select numbered Distinct values from Sql Server column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related