SQL Max Value for a Specified Limit

Question

I'm trying to return a list of years when certain conditions are met but I am having trouble with the MAX function and having it work with the rest of my logic.

For the following two tables:

          coach
coach | team | wins | year
------+------+------+------
nk01  | a    | 4    | 2000
vx92  | b    | 1    | 2000
nk01  | b    | 5    | 2003
vx92  | a    | 2    | 2003

           team
team | worldcupwin | year
-----+-------------+------
a    | Y           | 2000
b    | N           | 2000
a    | Y           | 2003
b    | N           | 2003

I want to get the following output:

years
-----
2000

Where the years printed are where the coaches' team with most wins during that year also won the world cup.

I decided to use the MAX function but quickly ran into the problem of not knowing how to use it to only be looking for max values for a certain year. This is what I've got so far:

SELECT y.year
FROM (SELECT c.year, MAX(c.wins), c.team
      FROM coach AS c
      WHERE c.year >= 1999
      GROUP BY c.year, c.team) AS y, teams AS t
WHERE y.year = t.year AND t.worldcupwin = 'Y' AND y.team = t.team;

This query outputs all years greater than 1999 for me, rather than just those where a coach with the most wins also won the world cup. (Using postgresql)

Any help is appreciated!

Fahmi · Accepted Answer · 2019-03-04T10:27:54.960

1

You can use correlated subquery

DEMO

   SELECT c.year, c.team
      FROM coachs AS c inner join teams t on c.team = t.team and c.year=t.year
      WHERE c.year >= 1999 and exists (select 1 from coachs c1 where c.team=c1.team 
      having max(c1.wins)=c.wins)

and t.worldcupwin = 'Y'

OUTPUT:

year    team
2000    a

edited Mar 04 '19 at 10:27

answered Mar 04 '19 at 07:59

Fahmi

37,315
5
22
31

What does the exists do here? I am narrowing down some answers but not all of them are correct. – Wintress Mar 04 '19 at 08:23
it will check whether the team has max win or not @Wintress – Fahmi Mar 04 '19 at 08:24
I see. Is this condition checking whether it's max win for that specific year? Or in general? – Wintress Mar 04 '19 at 08:29
do you need it year wise or in general @Wintress – Fahmi Mar 04 '19 at 08:56
Ok, this partially works but is ignoring the last part t.worldcupwin = 'Y', since I'm selecting from c.year, & c.team. So even when t.worldcupwin is N, it will still output a year. I also need it year wise! fa – Wintress Mar 04 '19 at 08:58
what is your expected output @Wintress, as I can see that worldcupwin = 'Y' for team b also - could you please clarify your question a bit more – Fahmi Mar 04 '19 at 09:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189379/discussion-between-fa06-and-wintress). – Fahmi Mar 04 '19 at 09:09
The expected output is only 2000 because team a had the highest wins and was also a world cup winner. 2003 is not included because even though team b did win the world cup, it did not have the highest number of wins per that tear. – Wintress Mar 04 '19 at 09:09

Giorgos Betsos · Answer 2 · 2019-03-04T10:42:36.550

The following query uses DISTINCT ON:

SELECT DISTINCT ON (year) c.year, wins, worldcupwin, c.team
FROM coach AS c
INNER JOIN team AS t ON c.team = t.team AND c.year = t.year 
WHERE c.year > 1999 
ORDER BY year, wins DESC

in order to return the records having the biggest number of wins per year

year    wins    worldcupwin team
---------------------------------
2000    4       Y           a
2003    5       N           b

Filtering out teams that didn't win the world cup:

SELECT year, team
FROM (
   SELECT DISTINCT ON (year) c.year, wins, worldcupwin, c.team
   FROM coach AS c
   INNER JOIN team AS t ON c.team = t.team AND c.year = t.year 
   WHERE c.year > 1999 
   ORDER BY year, wins DESC) AS t
WHERE t.worldcupwin = 'Y'       
ORDER BY year, wins DESC

gives the expected result:

year    team
-------------
2000    a

Demo here

score 1 · Answer 3 · answered Mar 04 '19 at 11:02

1

You can use the below to get the desired result:

EASY METHOD

SELECT TOP 1 c.year 

FROM coach AS c INNER JOIN team AS t ON c.team = t.team AND c.year = t.year

WHERE t.worldcupwin = 'Y'

ORDER BY c.wins DESC;

answered Mar 04 '19 at 11:02

Jithin Joy

118
4

Zaynul Abadin Tuhin · Answer 4 · 2019-03-04T08:05:45.187

0

use row_number() window function

select a.coach,a.team,a.win,a.year from 

 (select c.*,t.*,
row_number()over(order by wins desc) rn 
from  coach c join team t on c.team=t.team
 where worldcupwin='Y'
 ) a where a.rn=1

edited Mar 04 '19 at 08:05

answered Mar 04 '19 at 07:58

Zaynul Abadin Tuhin

31,407
5
33
63

SQL Max Value for a Specified Limit

4 Answers4