CODEWITHSUNDEEP
sqldatabasesqliterdbms
Pavan
Pavan

Reputation: 2851

Comparing rows of the same table with multiple conditions

Here is a table

ID | Player | Position | Points
1  | Ryan   | QB       | 75
2  | Matt   | RB       | 80
3  | Mike   | WR       | 66
4  | Jay    | QB       | 71
6  | Alvin  | TE       | 73
7  | Adrian | TE       | 84
8  | Hill   | WR       | 71
9  | Charles| RB       | 53
10 | Bell   | WR       | 87
11 | Rob    | TE       | 49
12 | Alex   | RB       | 92
13 | Drew   | QB       | 84
14 | Mack   | TE       | 59
15 | Nick   | WR       | 33

I want to report all the players in the position of the player having highest points and top 2 players of the other positions. In this example, "Alex" has the highest points and is a "RB". So I want to report all players from "RB" and top 2 from "QB", "TE", "WR" and order by points with in each group. I'm using sqlite3. I can do this programmatically using python and sqlite3 but i was wondering if this could be done only using sql

ID | Player | Position | Points
12 | Alex   | RB       | 92
2  | Matt   | RB       | 80
9  | Charles| RB       | 53
13 | Drew   | QB       | 84
1  | Ryan   | QB       | 75
10 | Bell   | WR       | 87
8  | Hill   | WR       | 71
7  | Adrian | TE       | 84
6  | Alvin  | TE       | 73

Thanks for your help

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is tricky in "traditional" SQLite. I would recommend union all:

with top1 as (
      select t.*
      from t
      order by points desc
      limit 1
     )
select t.*
from t
where t.position = (select t1.position from top1 t1)
union all
select t.*
from t
where t.position <> (select t1.position from top1 t1) and
      (select count(*)
       from t t2
       where t2.position = t.position and
             t2.points >= t.points
      ) <= 2;

This assumes that the points values are unique. Ties are much harder to deal with in SQLite.

I might recommend that you consider upgrading to SQLite version 3.25.0 or use another database. Such a query would be much simpler using ISO/ANSI standard window functions.

With window functions, it would look like:

select t.*
from (select t.*,
             row_number() over (partition by position order by points desc) as seqnum,
             first_value(position) over (order by points desc) as first_position
      from t
     ) t
where seqnum <= 2 or position = first_position

Upvotes: 1

Related Questions