-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy path29_versiontring_package.sql
112 lines (106 loc) · 3.72 KB
/
29_versiontring_package.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
/*
Please run utl_versiontring.pkb and .pks for this to work
*/
/* Sorting is now dead easy */
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
version,
utl_versionstring.sortable(version, 2) sortable_version
from input
order by utl_versionstring.sortable(version);
/* As is getting as getting a normalized, readable version */
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
utl_versionstring.readable(version) version
from input
order by utl_versionstring.sortable(version);
/* But what if we want to know the max version? */
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
utl_versionstring.readable(
max(utl_versionstring.sortable(version))
) as version
from input;
/*
Or if we want to know what major versions we have and how many versions belong to each major version?
*/
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
utl_versionstring.readable(
utl_versionstring.sortable_cut(version, 1)
) as version,
count(*) number_of_child_versions
from input
group by rollup ( utl_versionstring.sortable_cut(version, 1) )
order by utl_versionstring.sortable_cut(version, 1)
;
/*
Or the latest version of each major version?
*/
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
utl_versionstring.readable(
utl_versionstring.sortable_cut(version, 1)
) as major_version,
utl_versionstring.readable(
max(utl_versionstring.sortable(version))
) as latest_version
from input
group by ( utl_versionstring.sortable_cut(version, 1) )
order by utl_versionstring.sortable_cut(version, 1)
;