Top one for each group by

[SQLScholar]

Hey all,

I have this query - which isnt quite doing what i want.

SELECT  tblPolicyBrokerTeam.TeamName ,
        UserName
FROM    adw.policy.tblAcceptedByUser
        INNER JOIN adw.policy.tblPolicyBrokerTeam
            ON tblAcceptedByUser.BrokerTeamId = tblPolicyBrokerTeam.PolicyBrokerTeamId
ORDER BY UserStatus ,
        tblAcceptedByUser.ADWLoadDate DESC ,
        tblAcceptedByUser.ADWUpdateDate DESC

Basically my problem is i want to bring out the top teamname of this query for each username (ordered by the order by clause).

Now i keep thinking its something to do with RANK by but i cant just put my finger on what i need to do.

Any help much appreaciated.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[bborissov]

What is "Top team name" ???
How do you define it as TOP?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SQLScholar]

Sorry for not being clear. As per the order by clause.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

So effectively with my query if you took the first one of each username you came to, and removed the rest that would be perfect.

Ta

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

Aha..... I need Row_number not rank.

This seems to work:

SELECT *
FROM ( SELECT tblPolicyBrokerTeam.TeamName ,
UserName ,
ROW_NUMBER( ) OVER ( PARTITION BY UserName ORDER BY UserStatus , tblAcceptedByUser.ADWLoadDate DESC , tblAcceptedByUser.ADWUpdateDate DESC ) AS rowNum
FROM adw.policy.tblAcceptedByUser
INNER JOIN adw.policy.tblPolicyBrokerTeam
ON tblAcceptedByUser.BrokerTeamId = tblPolicyBrokerTeam.PolicyBrokerTeamId
) UserDefaultTeam
WHERE rownum = 1





----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------