Today I found solve problem to sort data in correct order. I say about char data content numeric data.
For example:
with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData
scol |
---|
10.1.1 |
1.1.1 |
2.1.1 |
2.10.1 |
2.2.1 |
If I use standard sort
with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData
order by scol
output:
scol |
---|
10.1.1 |
1.1.1 |
2.10.1 |
2.1.1 |
2.2.1 |
This is not right, but if I use split it's very cood output
with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData
order by
split_part(sCol,'.',1)::numeric,
split_part(sCol,'.',2)::numeric,
split_part(sCol,'.',3)::numeric
Output
scol |
---|
1.1.1 |
2.1.1 |
2.2.1 |
2.10.1 |
10.1.1 |
This is PostgreSQL code, but this technique can by used in other database.
Top comments (0)