Jeeva Jsb Jeeva Jsb - 5 months ago 14
SQL Question

How to select rows based on condition

The following is the code snippet.
Just design purpose I have added.

Here The user will be assigned multiple group.

So I want to select the person details alone.

Here Person id 103 have two different persmission for the same Product.
But the higher permission only be selected for the person.
But if he is not assinged to multiple group, the default permission should be selected.

Sample data



ProdId PersonId GroupId Permission

10103 78 55 15
10103 99 33 15
10103 100 33 0
10103 103 33 15
10103 103 40 0
10103 112 33 15





Result data should be



ProdId PersonId Permission

10103 78 15
10103 99 15
10103 100 0
10103 103 15
10103 112 15




Answer

You should use ROW_NUMBER() :

SELECT * FROM (
    SELECT t.*, 
           ROW_NUMBER() OVER(PARTITION BY t.prodid,t.personID ORDER BY t.permission DESC) as rnk
    FROM YourTable t) s
WHERE s.rnk = 1

I assumed you want the highest number on permission by your example? If not, change the ORDER BY clause to what you want.

Right now it will select all columns, specify the ones you want.