CODEWITHSUNDEEP
sqlt-sql
Jeeva J
Jeeva J

Reputation: 3253

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

Upvotes: 1

Views: 55

Answers (2)

The Batman
The Batman

Reputation: 21

If you are using Oracle, try the below query..

select * from (
select ProdID, PersonID, Permission, row_number() over (partition by PersonID order by Permission Desc) as column1 from table1)
where column1 = 1;

Upvotes: 0

sagi
sagi

Reputation: 40481

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.

Upvotes: 1

Related Questions