Click here to Skip to main content
15,893,161 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
It is getting executed but the output is wrong.

What I have tried:

select u.name from user as u,query as q where u.id=q.user_id group by u.name having count(q.user_id)=2 order by u.name;
Posted
Updated 5-Aug-17 1:59am

Take a look at the MAX() function;

SQL MIN() and MAX() Functions[^]
 
Share this answer
 
This is not easy - or at least, not easy to do if you don't want to group and count everything twice!
Assuming you have two tables (which would be the sensible way to do it)
Users   : Id, Name, OtherInfo
Queries : Id, UserId, OtherInfoAboutTheQuery

Then you can do it using a CTE and a couple of joins:
SQL
WITH AggData (UserId, UserCount)
AS (SELECT UserId, COUNT(UserId) as UserCount FROM Queries GROUP BY UserId) 
SELECT UserID, name FROM Users u
JOIN AggData ag ON u.Id = ag.UserID
JOIN (SELECT MAX(UserCount) As MaxCount From AggData) mx ON mx.MaxCount = ag.UserCount
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900