How to sort rows by rpm versions

selectsqlite

there are already some questions about how to sort version numbers. However, these do not deal with the RPM version scheme but often semver (Major, Minor, Patch). The RPM versioning specification is a bit more open and complex. For example it is also possible to use a date based version. Here is an example for how RPMs can be sorted by the version: link

Now to the real question, how can I sort and filter the records, taking into account the complexity of the version and releases, that I get a list in SQLite and PostgreSQL that contains the latest N RPM packages per RPM? Here are sample questions to solve:

  • What is the latest RPM pro package?
  • What are the latest two RPMs for the my-first-rpm package?
  • What are the new five RPMs?

I have the following table with records (sqlite):

CREATE TABLE packages (
  name                  VARCHAR(64)      NOT NULL,
  epoch                 INTEGER          NOT NULL,
  version               VARCHAR(32)      NOT NULL,
  release               VARCHAR(64)      NOT NULL,
  architecture          VARCHAR(16)      NOT NULL,
  CONSTRAINT pk_packages PRIMARY KEY (name, epoch, version, release, architecture)
);

INSERT INTO packages VALUES
("my-first-rpm", 0, "0.1.0", "0", "x86_64"),
("my-first-rpm", 0, "0.1.7", "0", "x86_64"),
("my-first-rpm", 0, "0.1.10", "0", "x86_64"),
("my-second-rpm", 0, "0.1.0", "0", "x86_64"),
("my-second-rpm", 1, "0.1.0", "0", "x86_64"),
("my-third-rpm", 0, "0.1", "0", "x86_64"),
("my-third-rpm", 0, "0.1.1", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128001", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128002", "0", "x86_64");

Best Answer

Maybe you need windowing functions like:

select
RANK () OVER ( 
    PARTITION BY name
    ORDER BY epoch desc, version desc, release desc
        ) namerank,
name,
epoch,
version,
release,
architecture
from packages
order by name,namerank;

But it does not list the versions in the correct order because it sees 0.1.7 as greater than 0.1.10 which it is as a character string. You might need version split into multiple columns of type number to get 0.1.10 greater than 0.1.7 or convert it to a number that keeps the order you want. I tested this on Oracle since I don't have sqllite or postgres handy. Output:

  NAMERANK NAME                                                                  EPOCH VERSION                          RELEASE                                                          ARCHITECTURE
---------- ---------------------------------------------------------------- ---------- -------------------------------- ---------------------------------------------------------------- ----------------
         1 my-date-based-rpm                                                         0 2021010128002                    0                                                                x86_64
         2 my-date-based-rpm                                                         0 2021010128001                    0                                                                x86_64
         1 my-first-rpm                                                              0 0.1.7                            0                                                                x86_64
         2 my-first-rpm                                                              0 0.1.10                           0                                                                x86_64
         3 my-first-rpm                                                              0 0.1.0                            0                                                                x86_64
         1 my-second-rpm                                                             1 0.1.0                            0                                                                x86_64
         2 my-second-rpm                                                             0 0.1.0                            0                                                                x86_64
         1 my-third-rpm                                                              0 0.1.1                            0                                                                x86_64
         2 my-third-rpm                                                              0 0.1                              0                                                                x86_64

Bobby

Related Question