Candidate matching in SQL

Question

I have the following table in my database.

# select * FROM 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 |    4 |    14
(11 rows)

Each person is ranked at work by different properties/criterias called 'prop' and the performance is called 'rank'. The table contains multiple values of (name, prop) as the example shows.

I want to get the candidates that fulfill ALL the requirements: having (prop=1 AND rank > 5) and (prop=3 AND rank >= 8).

Example 1

Input:

(prop=3 AND rank > 4), (prop=4 AND rank > 16)

Result:

name
-----
(none)

Example 2

Input:

(prop=1 AND rank > 6), (prop=2 AND rank > 4)

Result:

name
-----
alex

Example 3

Input:

(prop=1 AND rank > 2), (prop=2 AND rank > 2)

Result:

name
-----
carl
alex

Example 4

Input:

(prop=3 AND rank > 7)

Result:

name
----
carl
alex
anna

How can I do this in SQL?

It is not that simple, think you are misinterpreting my question — tkhduracell, Jun 18 '14 at 13:25
It's *not* trivial, but it's a duplicate. Improve your first question instead of posting a dupe. — Erwin Brandstetter, Jun 18 '14 at 13:43

score 1 · Answer 1 · answered Jun 18 '14 at 13:42

drop table if exists matches;

create table matches (name varchar, prop int, rank int);

insert into matches values
 ('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' ,    4 ,    14);

select
 name
from
 matches
group by
 name
having
 sum((prop=1 AND rank > 2)::int) > 0 and
 sum((prop=2 AND rank > 2)::int) > 0

This should handle both multiple rows for one property and missing values for property.

score -1 · Answer 2 · answered Jun 18 '14 at 13:22

-1

EX1:

SELECT    name
FROM      matches 
WHERE     (prop=3 AND rank > 4) OR (prop=4 AND rank > 16)

EX2:

SELECT    name 
FROM      matches 
WHERE     (prop=1 AND rank > 6) OR (prop=2 AND rank > 4)

etc... :-)

Or do you want to retriebe all examples-data in ONE query (which I think you do NOT want reading your question)?

answered Jun 18 '14 at 13:22

tim

9,896
20
81
137

Yes but (prop=3 AND rank > 4) OR (prop=4 AND rank > 16) will return Alex Alex Anna Anna 1. duplicates, 2. Neither Anna nor Alex fulfills the requirements – tkhduracell Jun 18 '14 at 13:37

Candidate matching in SQL

2 Answers2