Set Limit 3 to a SQL with Case and Left Join?-CodePudding

I have a working SQL code that retrieves all the scores of a hockey team. I would like to set Limit 3 or <= 3:

my = cursor_test.execute('''SELECT Next.ClubHome,
                            CASE
                            WHEN Next.ClubHome = Result.ClubHome then Result.ScoreHome
                            WHEN Next.ClubHome = Result.ClubAway then Result.ScoreAway
                            END as score
                            FROM NextMatch Next
                            LEFT JOIN ResultMatch Result ON Next.ClubHome in (Result.ClubHome, Result.ClubAway)
                            ''')

for row in my.fetchall():
    print(row)

Let me explain the question better:

Observe the next Chicago, New York and Dallas hockey matchs in the NextMatch table: are featured in ClubHome

NEXTMATCH

ClubHome	ClubAway	Tournament
Chicago	Minnesota	NHL
New York	Los Angeles	NHL
Dallas	Vegas Gold	NHL

In the ResultMatch table, I would like to retrieve the last 3 overall scores of Chicago, New York and Dallas (ScoreHome or ScoreAway). So I would like this output:

Chicago: 2

Chicago: 0

Chicago: 1

New York: 2

New York: 3

New York: 2

Dallas: 4

Dallas: 3

Dallas: 1

RESULTMATCH

ClubHome	ClubAway	Tournament	Round	ScoreHome	ScoreAway
Toronto	CHICAGO	NHL	8	1	2
New York	Vegas	NHL	8	2	3
CHICAGO	Dallas	NHL	7	0	4
Ottawa	New York	NHL	7	3	3
CHICAGO	Buffalo Sab	NHL	6	1	0
Vegas	CHICAGO	NHL	6	4	2
New York	Dallas	NHL	5	2	3
Dallas	Buffalo Sab	NHL	5	1	2

A code that can be USEFUL for the solution is the following. However, it only retrieves the last 3 Scorehome results (and not the ScoreAway):

x = cursor2.execute('''SELECT ClubHome,
                       FROM (SELECT NextMatch.ClubHome, NextMatch.ClubAway, ResultMatch.ScoreHome,
                       ROW_NUMBER() OVER (PARTITION BY NextMatch.ClubHome ORDER BY ResultMatch.ScoreHome DESC) AS rn
                       FROM NextMatch
                       INNER JOIN ResultMatch ON NextMatch.ClubHome = ResultMatch.ClubHome) t
                       WHERE rn <= 3
                       ORDER BY ClubHome ASC''')

How can I modify my (first code) and add Limit 3 or <= 3 to get what I ask for in the outputs example? Thank you

CodePudding user response：

If you want to do it in SQL only and not filtering the results in Python, you could use the windowing function ROW_NUMBER:

SELECT clubHome, score FROM (
SELECT Next.clubhome,
       CASE
         WHEN Next.ClubHome = Result.ClubHome then Result.ScoreHome
         WHEN Next.ClubHome = Result.ClubAway then Result.ScoreAway
       END as score, 
       ROW_NUMBER() OVER (PARTITION BY next.clubHome ORDER BY round DESC) rowNum
  FROM nextmatch Next
  JOIN resultmatch Result ON Next.clubhome in (Result.clubhome, Result.clubaway)
) WHERE rowNum <= 3;

SQLFiddle: https://www.db-fiddle.com/f/xrLpLwSu783AQHrwD8Fq4t/0