public inbox for [email protected]  

help / color / mirror / Atom feed

From: Viliam Ďurina <[email protected]>
To: [email protected]
Subject: MIN/MAX functions for a record
Date: Fri, 22 Mar 2024 12:26:27 +0100
Message-ID: <CAO=iB8L4WYSNxCJ8GURRjQsrXEQ2-zn3FiCsh2LMqvWq2WcONg@mail.gmail.com> (raw)

In my queries I often need to do MIN/MAX for tuples, for example:

  SELECT MAX(row(year, month))
  FROM (VALUES(2025, 1), (2024,2)) x(year, month);

This query throws:

    ERROR: function max(record) does not exist

In this case you can replace it with `MAX((year||'-'||month||'-1')::date)`.
However in my case I have an event table with `event_time` and `text`
columns, I'm grouping that table by some key and want to have the text for
the newest event. I would do `MAX(ROW(event_time, text)).text`. Workarounds
for this are clumsy, e.g. with a subquery with LIMIT 1.

The lack of this feature is kind of unexpected, because the `>` operator or
`GREATEST` function are defined for records:

    SELECT
        GREATEST((2025, 1), (2024, 2)),
        (2025, 1) > (2024, 2)

Was this ever discussed or is there something preventing the implementation?

Viliam

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: MIN/MAX functions for a record
  In-Reply-To: <CAO=iB8L4WYSNxCJ8GURRjQsrXEQ2-zn3FiCsh2LMqvWq2WcONg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox