Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 4,406, Visits: 6,270
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, October 21, 2013 1:47 AM
Points: 114, Visits: 209
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