select * from (
select 'lkj' name, 1 isSup, 0 isHse, 1 isMgr
union
select 'lky' name, 0 isSup, 0 isHse, 1 isMgr
union
select 'Jon' name, 0 isSup, 1 isHse, 0 isMgr
union
select 'Kim' name, 1 isSup, 0 isHse, 0 isMgr
) myTable
where isSup in (1) and isHse in (0,1) and isMgr in (0,1)
select row_number() over (partition by groupid order by date) r from test_table
