Home > Net >  Postgres query selection logic
Postgres query selection logic

Time:11-24

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?

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:

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

  • Related