Postgres query selection logic

Question

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:

id	edition	major	minor	patch	cycle	is_latest_bedrock	is_latest_java
ddcdc01f-7ac1-4c4a-be7f-5e93902a0855	b	1	16	0	1.16	true	false
20d1bf38-75d6-4d96-94fc-fd16d2131319	b	1	17	0	1.17	true	false
13252697-4fe6-411f-b151-e4a1ca146e2f	b	1	18	0	1.18	true	false
16a1eb78-e566-4649-991c-3ecdd8e6f49b	b	1	19	0	1.19	true	false
5ef4657a-c4fc-41f4-b2e1-0aa88e0e4b07	j	1	16	0	1.16	false	true
f68cebf4-a62d-45c5-af67-098f8be041a3	j	1	17	0	1.17	false	true
bd37ff94-5a62-4fc7-a729-6fc353a7c939	j	1	18	0	1.18	false	true
09293db6-aa6b-4cc4-8a58-29afba816d85	j	1	19	0	1.19	false	true

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

id	edition	major	minor	patch	cycle	is_latest_bedrock	is_latest_java
ddcdc01f-7ac1-4c4a-be7f-5e93902a0855	b	1	16	0	1.16	false	false
20d1bf38-75d6-4d96-94fc-fd16d2131319	b	1	17	0	1.17	false	false
13252697-4fe6-411f-b151-e4a1ca146e2f	b	1	18	0	1.18	false	false
16a1eb78-e566-4649-991c-3ecdd8e6f49b	b	1	19	0	1.19	true	false
5ef4657a-c4fc-41f4-b2e1-0aa88e0e4b07	j	1	16	0	1.16	false	false
f68cebf4-a62d-45c5-af67-098f8be041a3	j	1	17	0	1.17	false	false
bd37ff94-5a62-4fc7-a729-6fc353a7c939	j	1	18	0	1.18	false	false
09293db6-aa6b-4cc4-8a58-29afba816d85	j	1	19	0	1.19	false	true

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

Answer 1

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:

id	edition	major	minor	patch	cycle	is_latest_version_for_edition
6ef44cf8-10d6-4cb9-874a-ff9322ce708b	b	1	16	0	1.16	false
9d5fc712-819f-4d9d-ada7-fa5371ae1a7e	b	1	17	0	1.17	false
046c76f2-c8be-4686-ab0d-1387fd798579	b	1	18	0	1.18	false
3ba9b494-acf4-4eda-ae46-b305abebf93d	b	1	19	0	1.19	true
87c2552b-3591-416a-baa1-6297b3b0e6c2	j	1	16	0	1.16	false
51872b9b-ec3f-45d3-8be7-532946b699a8	j	1	17	0	1.17	false
2205b1df-4d7b-4eee-ac17-c153f2374339	j	1	18	0	1.18	false
f65635e4-e687-4a30-a69d-9154b6d61dd6	j	1	19	0	1.19	true

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