TIL: Finding a row with the max of multiple columns in Postgresql

Window functions¹ are a little-used feature of Postgresql that allow you to apply aggregate functions over groups of records based on matching values in a column. In other words, if we wanted to tally a department’s salaries by department, we could SUM the salaries using a windowing function OVER a PARTITION defined by a department column. We can also tell the window function how to order the records within each department section, so we could (for example) tally the salaries progressively by pay grade.

Or, to apply this to our values table, we can create a function to aggregate value over records grouped by collection in which rows are ordered by book, chapter, and line. It might look like this:

SELECT DISTINCT collection, 
       FIRST(value) OVER (PARTITION BY collection 
			  ORDER BY book DESC, chapter DESC, line DESC) 
FROM values;

What this query does is group (PARTITION) our records by collection, order each group as we specified in our OVER clause, and then run the FIRST aggregate function on the value field in each group (FIRST is an aggregate function that gives us the first record in the series). Unlike a similar GROUP BY operation, this still gives us a row for each row in the original table; but since the aggregated value will be the same for each group, a DISTINCT will reduce us down to one row per collection.

There’s only one real problem with this query: FIRST doesn’t exist (sorry copy-pasters).

As nice as it would have been, there’s no aggregate function that will return the first record in a series. Is there a way around this?

Array types in a database may at first just seem like a dangerous tool for thwarting normalization, but there are situations where they come in quite handy. This is one of them. Postgresql has an array_agg() aggregate function that, well, aggregates values into an array. Thus, the query:

SELECT collection, array_agg(value) FROM values GROUP BY collection;

Returns:

 collection |    array_agg     
------------+------------------
       1234 | {fizz,blah}
       1235 | {quuz,qaax,fooz}

Now, by using a window function instead of a GROUP BY, we can control the order that the values end up in the array:

SELECT collection, 
       array_agg(value) 
	 OVER (PARTITION BY collection 
	       ORDER BY book DESC, chapter DESC, line DESC) 
FROM values;

This gives us:

 collection |    array_agg     
------------+------------------
       1234 | {fizz}
       1234 | {fizz,blah}
       1235 | {fooz}
       1235 | {fooz,qaax}
       1235 | {fooz,qaax,quuz}

Ah, now you can see that the first value in each array is the correct one. We just need to pop off that first value and add DISTINCT so that we only have one record per collection:

SELECT DISTINCT collection, 
       (
	array_agg(value) 
	OVER (PARTITION BY collection 
	      ORDER BY book DESC, chapter DESC, line DESC)
	)[1] 
FROM values;

Notice two things here:

• Unlike every other array/list/tuple/collection syntax known to programmer-kind, Postgresql arrays start at 1, not 0. So the first value in the array is 1.
• We need to put the whole window expression in parenthesis and add the subscript to that, because it’s the whole expression (not just array_agg(value)) that evaluates to the array.

This query gives us the correct output, and is considerably more approachable than the fractal approach. Not only that, but adding more ORDER BY columns is trivial.