/    Sign up×
Community /Pin to ProfileBookmark

Two MIN and MAX queries

Copy linkTweet thisAlerts:
Jun 04.2021

I’ve got a MYSQL query. Selecting FROM a ‘matchResults ‘table but some fields are coming from matchData, teamData tables.

In my matchResults table I have resultDate.

Currently this is part of my query which works nicely.

[code]
SELECT DISTINCT rd.playerId, t.teamName, t.teamId, MIN(rd.resultDate) as FD, MAX(rd.resultDate) as LD,
[/code]

Where FD and LD are finding the first and last dates for a team when the teamId is given in the WHERE clause.

Some results (game outcomes) have a indicator on them, indicating the match was played for a shield. the Column is called ‘margsResult’ which is either 1 or 0.

I’m trying to also result the first and last dates of these matches. Tried doing this.

[code]
CASE WHEN g.margsResult = 1 THEN MIN(rd.resultDate) ELSE 0 END as FTG,
[/code]

But I still get the lowest or earliest date.

Any ideas?

to post a comment

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogJun 04.2021 — Probably need more info (like maybe the whole query?). Presumably there's some group by action going on, and no idea how you're joining whatever g is?
Copy linkTweet thisAlerts:
@kiwisauthorJun 04.2021 — Sure, I guess what I'm trying to do is return two dates, possibly from two different rows in one single query.
<i>
</i>SELECT DISTINCT rd.playerId, t.teamName, t.teamId, MIN(rd.resultDate) as FD, MAX(rd.resultDate) as LD,
SUM(md.scores) as Scores,
SUM(md.Bonus) as Bns,

IF(rd.homeaway = 1, mr.team1Id, mr.team2Id) as myteamId <br/>
FROM <span><code>matchResults</code></span> mr <br/>
JOIN <span><code>resultData</code></span> rd ON mr.gameId = rd.gameId
JOIN <span><code>teams</code></span> t ON t.teamId = IF(rd.homeaway = 1, mr.team1Id, mr.team2Id)
WHERE <span><code>playerId</code></span> = ? AND <span><code>didntPlay</code></span> = 0 AND t.teamType = ?

GROUP BY rd.playerId, t.teamName <br/>
ORDER BY MIN(mrkickOff) ASC";
Copy linkTweet thisAlerts:
@NogDogJun 06.2021 — Sorry for the delay, but -- still not clear to me where g.margsResult comes from?
Copy linkTweet thisAlerts:
@kiwisauthorJun 06.2021 — @NogDog#1632616

that's alight. I've removed the dates from my display now but would add them in if I can. (I don't believe I can from my query).

margsResult is a field in the matchResults table. It's eith 1 or 0, 0 by default.
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.26,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...