I am a regular follower of ASKTOM and OTN. I found a interesting question regarding Sorting the Versions which I thought could be a very good candidate to be on this Puzzle series. I am hoping that you will also like it and will enjoy solving it.
Lets first create the table and data for this.
Here goes the problem Statement:
I want to sort data of versions table by individual number separated by Dot of version column. below is the result I need:
Here is the solution which I have created
You can put your answers on the comment box. Enjoy solving it :)
More SQL Puzzles:
- SQL Puzzle - Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers
Lets first create the table and data for this.
SQL> create table versions
2 (
3 version varchar2(30)
4 );
Table created.
SQL> insert into versions values ('1.1');
1 row created.
SQL> insert into versions values ('1.1.1');
1 row created.
SQL> insert into versions values ('1.2.10');
1 row created.
SQL> insert into versions values ('1');
1 row created.
SQL> insert into versions values ('1.10.1');
1 row created.
SQL> insert into versions values ('1.1.2');
1 row created.
SQL> insert into versions values ('2.1');
1 row created.
SQL> insert into versions values ('2');
1 row created.
SQL> insert into versions values ('1.10.2');
1 row created.
SQL> insert into versions values ('1.2');
1 row created.
SQL> insert into versions values ('1.2.4');
1 row created.
SQL> insert into versions values ('1.2.5');
1 row created.
SQL> commit;
Commit complete.
Here goes the problem Statement:
I want to sort data of versions table by individual number separated by Dot of version column. below is the result I need:
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2
2.1
Here is the solution which I have created
SELECT version
FROM versions
ORDER BY TO_NUMBER (REGEXP_SUBSTR (version, '\d+')),
TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,2)) NULLS FIRST,
TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,3)) NULLS FIRST;
You can put your answers on the comment box. Enjoy solving it :)
More SQL Puzzles:
- SQL Puzzle - Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers
order by to_number(regexp_substr(version, '[[:digit:]]+', 1, 1)) nulls first,
ReplyDeleteto_number(regexp_substr(version, '[[:digit:]]+', 1, 2)) nulls first,
to_number(regexp_substr(version, '[[:digit:]]+', 1, 3)) nulls first
Edgaras Žuklys
DBA at LB
order by regexp_replace(regexp_replace(version,'([[:digit:]]+)','00001'),'0*([[:digit:]]{3})','1')
ReplyDeleteIn the old days before REGEXP it could still be done.But it was a pain!
ReplyDeleteORDER BY
CASE WHEN INSTR(version,'.') = 0 THEN TO_NUMBER(version)
ELSE TO_NUMBER(SUBSTR(version,1,INSTR(version,'.',1,1)-1)) END
,CASE WHEN INSTR(version,'.',1,1) = 0 THEN 0
ELSE TO_NUMBER(SUBSTR(version,INSTR(version,'.',1,1)+1,INSTR(version,'.',1,2)-INSTR(version,'.',1,1)-1))
END
,CASE WHEN INSTR(version,'.',1,2) = 0 THEN 0
ELSE TO_NUMBER(SUBSTR(version,INSTR(version,'.',1,2)+1))
END