postgresql - Matching algorithm in SQL -

postgresql - Matching algorithm in SQL -

i have next table in database.

# select * matches; name | prop | rank ------+------+------- carl | 1 | 4 carl | 2 | 3 carl | 3 | 9 alex | 1 | 8 alex | 2 | 5 alex | 3 | 6 alex | 3 | 8 alex | 2 | 11 anna | 3 | 8 anna | 3 | 13 anna | 2 | 14 (11 rows)

each person ranked @ work different properties/criterias called 'prop' , performance called 'rank'. table contains multiple values of (name, prop) illustration shows. want best candidate next requirements. e.g. need candidate have (prop=1 , rank > 5) , (prop=3 , rank >= 8). must able sort candidates rankings best candidate.

edit: each person must fulfill requirements

how can in sql?

select x.name, max(x.rank) matches x bring together ( select name matches prop = 1 , rank > 5 intersect select name matches prop = 3 , rank >= 8 ) y on x.name = y.name grouping x.name order max(rank);

sql postgresql relational-division