fetch single row with value true for grouped records mysql

Question

My Sample data:

id     role_id    permission_id      access_granted      user_id
------------------------------------------------------------------
1         1            10                  1               115
------------------------------------------------------------------
2         1            5                   1               115   
------------------------------------------------------------------
3         2            10                  0               115
------------------------------------------------------------------
4         2            8                   0               115
------------------------------------------------------------------

Group by with permission_id and the result should have value true row for the repeated permission_id. As we can see the query is grouped by permission_id and the row with value true is selected for permission_id 10 and for other rows permission id is not being repeated so the value false is being picked as it is.

Expected result:

id     role_id    permission_id      access_granted      user_id
------------------------------------------------------------------
1         1            10                  1               115
------------------------------------------------------------------
2         1            5                   1               115   
------------------------------------------------------------------
4         2            8                   0               115
------------------------------------------------------------------

Below is a query that I tried to fetch the above result.

select id, role_id, permission_id,
case when max(access_granted) = true then true else false end as access_granted,
user_id as user_id  
from temp_permission group by permission_id;

Need optimized and authentic query that will not fail in any case.

Create table query

CREATE TABLE temp_permission (
    id int auto_increment primary key,
    role_id int,
    permission_id int,
    access_granted tinyInt(1),
    user_id int
);

Dummy data query for the table as per the screenshot

INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('1', '1', '10', '1', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('2', '1', '5', '1', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('3', '2', '10', '0', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('4', '2', '8', '0', '115');

if you are unable to insert data into access_granted column replace 1 = b'1' & 0 = b'0'

Answer 1

You can simply order the rows based on permission_id and access_granted in descending order, and then select distinct rows based on permission_id. Something like:

WITH ordered_rows AS (
  SELECT id, role_id, permission_id, access_granted, user_id,
         ROW_NUMBER() OVER (PARTITION BY permission_id ORDER BY access_granted DESC) as row_num
  FROM temp_permission
)
SELECT id, role_id, permission_id, access_granted, user_id
FROM ordered_rows
WHERE row_num = 1;

DB Fiddle