CREATE TYPE edition AS ENUM (
    'b',
    'j'
);

CREATE TABLE IF NOT EXISTS versions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    edition edition NOT NULL,
    major integer NOT NULL,
    minor integer NOT NULL,
    patch integer NOT NULL,
    cycle decimal GENERATED ALWAYS AS (
        CAST(
            (CAST(major AS text) || '.' || CAST(minor AS text)) AS decimal
        )
    ) STORED
);

INSERT INTO versions
    (edition, major, minor, patch)
VALUES
    ('b', 1, 16, 0),
    ('b', 1, 17, 0),
    ('b', 1, 18, 0),
    ('b', 1, 19, 0),
    ('j', 1, 16, 0),
    ('j', 1, 17, 0),
    ('j', 1, 18, 0),
    ('j', 1, 19, 0)
;

I'm building a web application to reference various aspects of Minecraft. I'm trying to build a stored generated column in my versions table to specify if the record in question specifies the highest version number, per edition. (Minecraft is published as two separate and incompatible editions: Bedrock (b) and Java (j).)

Given the structure and data above, I expect the following select to return true only once for each is_latest_* field and false for all others.

SELECT
    *,
    (
        edition = 'b'
        AND GREATEST(major) = major
        AND GREATEST(minor) = minor
        AND GREATEST(patch) = patch
    ) AS is_latest_bedrock,
    (
        edition = 'j'
        AND GREATEST(major) = major
        AND GREATEST(minor) = minor
        AND GREATEST(patch) = patch
    ) AS is_latest_java
FROM versions
ORDER BY edition, major, minor, patch;

What I am instead seeing is that every record is marked as the highest version for each respective edition:

Here is the result set I expect/want to see:

How can I update the query to mark only one Bedrock version and one Java version as the latest?

CodePudding user response：

ROW_NUMBER window function, and partitioning over the edition types may be a good solution for this case - where you want to still return all records.

See the below query, which windows over the edition types and assigns a row number to each record, sorted by major, minor, patch in descending order. For generating a boolean for the latest result, = 1 is used to just assert whether this record is the first in the ordering of the window. A pro to this route is it will output an indicator for any edition type in the table that may be added in the future.

Query:

SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY edition ORDER BY major desc, minor desc, patch desc) = 1 as is_latest_version_for_edition
FROM versions
ORDER BY edition, major, minor, patch;

Result:

DbFiddle: https://www.db-fiddle.com/f/sB7zE1syp8xNmnEk2oizCR/0