CODEWITHSUNDEEP
phpmysqlphpmyadmin
Simone
Simone

Reputation: 311

Query previous and next match

I have a phpmyadmin database with a table "CALENDAR", that contains the list of all games between teams.

This is the structure: ID, TEAM_HOME, TEAM_AWAY, DATE, RESULT_HOME, RESULT_AWAY

I need to create a mysql query that return only 2 records: the previous game (so where the fields RESULT_HOME and RESULT_AVAY are <> '0'); the next game (the game that will be played closer).

Please, can you help me with the query? I've tried with this one, but it isn't correct for me:

SELECT C.*
FROM CALENDAR AS C
WHERE C.DATA BETWEEN DATE_SUB(CURRENT_DATE,  INTERVAL '7' DAY) AND CURRENT_DATE
ORDER BY YEAR(C.DATA) ASC, MONTH(C.DATA) ASC, DAY(C.DATA) ASC
LIMIT 2

If you need further details, please let me know

Upvotes: 3

Views: 60

Answers (3)

Simone
Simone

Reputation: 311

I've go this:

(SELECT * FROM CALENDAR AS C WHERE RESULT_HOME <> 0 AND RESULT_AWAY <> 0 ORDER BY C.DATA DESC LIMIT 1)

UNION

(SELECT * FROM CALENDAR AS C WHERE RESULT_HOME = 0 OR RESULT_AWAY = 0 ORDER BY C.DATA ASC LIMIT 1)

Thank you!

Upvotes: 1

MvG
MvG

Reputation: 60858

To obtain the latest (in terms of date) game having non-zero results, you order restrict to those non-zero results, order by date and grab the single row with the largest date, i.e. the first row if you order descending:

SELECT … FROM CALENDAR AS C
WHERE … AND (RESULT_HOME <> 0 AND RESULT_AVAY <> 0)
ORDER BY C.DATA DESC
LIMIT 1

Likewise for the opposite case: smallest date with one zero result would be this:

SELECT … FROM CALENDAR AS C
WHERE … AND (RESULT_HOME = 0 OR RESULT_AVAY = 0)
ORDER BY C.DATA ASC
LIMIT 1

To combine them into a single query, you can use a UNION. But I'd prefer two distinct queries.

Sorting by the different fields of the date independently just makes things complicated for the DBMS, without any benefit. So just sort by date.

Upvotes: 2

reyjoel
reyjoel

Reputation: 29

if you want to return last two game?

if that the case try DESC LIMIT 2

Upvotes: 0

Related Questions