Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlreader
Starting Member
1 Post |
 Posted - 2011-02-22 : 12:54:28
|
I have a table:
UserId, UserGroup, ConnectionsNumber
UserId is unique but UserGroup could be repeated.
As an example, the table is filled like this:
1 1 3
2 1 4
3 1 5
4 2 1
5 3 10
I need to select only 1 record per user group and it must have the maximum connectionNumber
If I do it like this:
select UserGroup, UserId, max(connectionsNumber)
from table
group by UserGroup, UserId
then I get several records...
And If I select select UserGroup, max(connectionsNumber)
from table
group by UserGroup
then it's fine but I'm missing UserId field...
Please correct my query, I know it must be very simple
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-22 : 14:13:54
|
You should post in the New to SQL forum, you might get overlooked here
I am assuming you're on 2005 or later
SELECT *
FROM
(
select userid,usergroup, ConnectionsNumber
,[rank] = rank() over(partition by usergroup order by ConnectionsNumber desc)
from yourTable
) a
WHERE [rank] = 1
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
|
|
|
Very simple sql query