CODEWITHSUNDEEP
mysql
James Veinte
James Veinte

Reputation: 17

Find first row per year that meets criterion in MySQL table

I have a MySQL database that keeps track of sports stats. I'm trying to make a query that will pull out just the rows with the first home game of each season. The database is set up similar to this:

TABLE 'STATS'
GAMENUMBER | YEAR | MONTH | DAY | OPPONENT | HOMEAWAY | ...
1          | 2010 | 9     | 7   | Yale     | A        | ...
2          | 2010 | 9     | 14  | Rice     | H        | ...
3          | 2010 | 9     | 21  | NCSU     | H        | ...
1          | 2009 | 9     | 7   | Ohio     | A        | ...
2          | 2009 | 9     | 14  | SUNY     | A        | ...
3          | 2009 | 9     | 21  | Penn     | A        | ...
1          | 2008 | 9     | 7   | Iowa     | H        | ...
...

I assume I need to somehow use the YEAR and GAMENUMBER columns to find the single game per year that is the first home game. But I can't get the query right! If it helps this will be called using PHP on a website to make a table showing the data.

Thanks very much for any help!

Upvotes: 1

Views: 364

Answers (2)

james_bond
james_bond

Reputation: 6908

I think this will do:

SELECT MIN(s.gamenumber),s.year,s.month,s.day,s.homeaway
FROM stats s
WHERE s.homeaway='H'
GROUP BY s.year

let me know if it works cause i can't test it right now...

Upvotes: 0

ain
ain

Reputation: 22749

Query like

SELECT year, min(GAMENUMBER) FROM stats WHERE(HOMEAWAY = 'H') GROUP BY year

would get you first homegame per year but to get the rest of the row too I would try

SELECT s.*
FROM stats s
  JOIN(SELECT year y, min(GAMENUMBER) gn FROM stats WHERE(HOMEAWAY = 'H')GROUP BY year) q
    ON(s.year = q.year)AND(s.GAMENUMBER = q.gn)

Upvotes: 1

Related Questions