CODEWITHSUNDEEP
sqlaggregatenon-equi-join
Barzee
Barzee

Reputation: 905

SQL for Grouping Teams by Rank

Given two tables named team and award, I need to assign teams to an award based on each team's rank. Here are the two tables:

team

team_id name rank
1 stars 4
2 worms 2
3 birds 1
4 dogs 3
5 clouds 5
6 broncos 8
7 cars 11
8 llamas 7
9 cats 9
10 locusts 6
11 bulls 10

award

threshold amount
1 10000.00
2 7000.00
3 5000.00
6 2000.00
10 500.00

The teams must be assigned to awards like this:

rank threshold amount
1 1 10000.00
2 2 7000.00
3 3 5000.00
4 6 2000.00
5 6 2000.00
6 6 2000.00
7 10 500.00
8 10 500.00
9 10 500.00
10 10 500.00
11 null null

I've never used a non-equi join before but thought this might be a classic example where a non-equi join would be helpful. I wrote this MySQL query which gives the results that I want:

SELECT t.name, t.rank, MIN(a.threshold) AS threshold, MAX(a.amount) AS amount
FROM team AS t
    INNER JOIN award AS a ON t.rank <= a.threshold
GROUP BY t.team_id
ORDER BY t.rank;
name rank threshold amount
birds 1 1 10000.00
worms 2 2 7000.00
dogs 3 3 5000.00
stars 4 6 2000.00
clouds 5 6 2000.00
locusts 6 6 2000.00
llamas 7 10 500.00
broncos 8 10 500.00
cats 9 10 500.00
bulls 10 10 500.00

My question is, "Are there problems with using a non-equi join that I don't understand?" Or in other words, "Is there a more standard SQL approach to this problem?"

Upvotes: 0

Views: 71

Answers (1)

LukStorms
LukStorms

Reputation: 29677

Also standard, but not a feature in every RDBMS, is a LATERAL JOIN.

SELECT t.team_id, t.name, t.rank
, award.threshold
, award.amount
FROM team t
CROSS JOIN LATERAL (
    select
      min(a.threshold) as threshold
    , max(a.amount) as amount
    from award a
    where a.threshold >= t.rank
) award
ORDER BY t.rank;

It's similar to a correlated sub-query.
But you can get more columns from it.

Upvotes: 1

Related Questions