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)