CODEWITHSUNDEEP
sql-serversql-server-2005
Tech VS
Tech VS

Reputation: 13

Get top rows based on highest records

I have to tables named "Category" and "Users" i'm inserting record in user table under some categories, Now i want order categories under which maximum users registered.

Sample table;

Category:

CategoryId  Name
1           Category1
2           Category2
3           Category3

Users:

UserId      Name       CategoryId
1           User1           1
2           User2           2
3           User3           2
4           User4           2
5           User5           3

Now in above example CategoryId 2 contains 3 users so i want CategoryId 2 Name on top.

Thanx

Upvotes: 0

Views: 54

Answers (2)

Shuhel Ahmed
Shuhel Ahmed

Reputation: 963

select count(CategoryId) as rank, CategoryId 
from users group by Categoryid order by rank desc

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

SELECT
  *
FROM
  Category
ORDER BY
  (SELECT COUNT(*) FROM Users WHERE CategoryId = Category.CategoryId) DESC


Or alternatively, as some perfer this, though I do not...

SELECT
  Category.CategoryId,
  Category.Name
FROM
  Category
LEFT JOIN
  Users
    ON Users.CategoryId = Category.CategoryId
GROUP BY
  Category.CategoryId,
  Category.Name
ORDER BY
  COUNT(Users.CategoryID) DESC

Upvotes: 2

Related Questions