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

Question

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?

Michael Krelin - hacker · Accepted Answer · 2011-12-18 21:34:41Z

4

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)

edited Dec 18, 2011 at 21:34

answered Dec 18, 2011 at 21:28

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GrumpyCanuck Over a year ago

I went with the second one, and I must admit I thought about using GROUP BY and ORDER but didn't think it would work. :P

Michael Krelin - hacker Over a year ago

Why wouldn't it? ;-) Have you checked with explain if the second one is better? Just wondering…

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related