CODEWITHSUNDEEP
postgresql
GrumpyCanuck
GrumpyCanuck

Reputation: 1438

Getting a count of number of rows matching MAX() value in Postgres

I have a table called 'games' that has a column in it called 'week'. I am trying to find a single query that will give me the maximum value for 'week' along with a count of how many rows in that table have the maximum value for 'week'. I could split it up into two queries:

SELECT MAX(week) FROM games
// store value in a variable $maxWeek
SELECT COUNT(1) FROM games WHERE week = $maxWeek
// store that result in a variable

Is there a way to do this all in one query?

Upvotes: 1

Views: 3072

Answers (1)

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143119

SELECT week, count(*) FROM games GROUP BY week ORDER BY week DESC LIMIT 1;

or

SELECT week, count(*) FROM games WHERE week = (SELECT max(week) FROM games) GROUP BY week;

(may be faster)

Upvotes: 4

Related Questions