Nested query

  • Thread starter Thread starter Stian Berg
  • Start date Start date
S

Stian Berg

Hello

I have two tables, one for users and one for messages that the users have
written. I want to find the user who has written the most messages.

The tables:
User { *UserID, UserName, ... }
Message { *MessageID, UserID, MessageDate, Message, ... }

Primary keys is marked with *, and Message.UserID is of course the
foreign key from User.

The statement below will find the number of messages that the most active
user has written, but not which user this is. How can I do this?


SELECT MAX(AntMeldinger)
FROM ( SELECT COUNT(*) AS AntMeldinger
FROM User AS U, Message AS M
WHERE U.UserID =M.UserID
GROUP BY U.UserID
);


Thanks for your help.
 
(...)
Click to expand...

Answering my own post I got this solution from an other board that works
for me.

SELECT TOP 1 User.UserID, Count(*)
FROM User, Message
WHERE User.UserID=Message.UserID
GROUP BY B.BrukerID
ORDER BY 2 DESC;


Still, if you have any suggestions/comments please feel free join in.
 
Back
Top