Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Help On Query Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:15 PM
Points: 5,358, Visits: 7,769
For those using ROW_NUMBER, if you want users having 2 OR MORE roles, shouldn't the filter be rownum >= 2 instead of just rownum = 2?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1493298
Posted Friday, September 13, 2013 5:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 28, 2015 9:18 AM
Points: 121, Visits: 221
To avoid the duplicate results use rank().

WITH CTEROLES 
AS(
SELECT Name,Role,RANK() OVER( PARTITION BY Name ORDER BY Role) AS NumberofRoles FROM Sample
)
SELECT Name,NumberofRoles FROM CTEROLES WHERE NumberofRoles>1

Post #1494548
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse