Clever Geek Handbook

Ordering digital dot patterns (such as version numbers)

In my database, I ran into this data:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.10
1.11
1.2
1.9

I want to sort it to get the result looks like this:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.2
1.9    
1.10
1.11

How can i do this? Simple use of "ORDER BY" gives the wrong result because it is lexicographic order.

+3


source to share


2 answers


You can split a string into an array, apply it to, int[]

and rely on Postgres' natural ordering for arrays:



SELECT   mycolumn
FROM     mytable
ORDER BY STRING_TO_ARRAY(mycolumn, '.')::int[] ASC
+5


source


The listing cidr

will do the trick (if the numbers behave well)

DROP table meuk;
CREATE table meuk
        ( id SERIAL NOT NULL PRIMARY KEY
        , version text
        );


INSERT INTO meuk(version) VALUES
 ('1' )
, ('1.1' )
, ('1.1.1' )
, ('1.1.1.1' )
, ('1.1.2' )
, ('1.10' )
, ('1.11' )
, ('1.2' )
, ('1.9' )
        ;

SELECT * FROM meuk
ORDER BY version::cidr
        ;


Result:

INSERT 0 9
 id | version 
----+---------
  1 | 1
  2 | 1.1
  3 | 1.1.1
  4 | 1.1.1.1
  5 | 1.1.2
  8 | 1.2
  9 | 1.9
  6 | 1.10
  7 | 1.11
(9 rows)
+1


source






More articles:


All Articles