0

Hi I have a synerio in that user mapped to two roles and roles have priority set like 1 and 2 ...my question is how to access highest code and role only ...

For exp 

Code       Name          Role   RolePriority
1          Rehman        Abc    2
2          Rehman        Def    3
3          Neha          Des    1

So i want Rehman with Def role and Neha with Des role

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Niteesh Kumar
  • 213
  • 3
  • 18
  • Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – O. Jones Apr 15 '18 at 18:53

1 Answers1

0

You can use ROW_NUMBER() OVER() to select highest priority

DECLARE @test AS TABLE(Code INT,Name VARCHAR(100),Role VARCHAR(100),RolePriority int)

INSERT INTO @test VALUES(1,'Rehman','Abc',2),(2,'Rehman','Def',3),(3,'Neha','Des',1)

SELECT * FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY RolePriority desc) AS 
rw FROM @test)res
WHERE rw = 1
mhsankar
  • 423
  • 5
  • 18