0

how would you write sql to order by a text field called "sequentialOrder" with values like 7.5.5 so that the records come out in the order of 

    1.2.4
    2.3.8
    11.3.4

and not like this

    11.3.4         
    1.2.4
    2.3.8
Beth B
  • 59
  • 2
  • Use a numeric sensitive collation. – jarlh Sep 07 '16 at 14:12
  • is the string format fixed? does it always have 3 numbers separated by `.`s? – Vamsi Prabhala Sep 07 '16 at 14:12
  • are you always going to have 2 decimals in there? – Rich Benner Sep 07 '16 at 14:12
  • to me you shouldn't even try to do it since I don't think you can make use of index when you use custom sort – Steve Sep 07 '16 at 14:14
  • @Steve, depends on which dbms OP is using. – jarlh Sep 07 '16 at 14:15
  • If always same format of data try this -> http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php – The One and Only ChemistryBlob Sep 07 '16 at 14:16
  • 2
    Please tag RDBMS you are using like SQLServer,oracle,mysql and so on – TheGameiswar Sep 07 '16 at 14:17
  • `order by string_to_array(the_column,',')::int` –  Sep 07 '16 at 14:18
  • This should be relevant: https://stackoverflow.com/questions/528830/sql-sort-by-version-number-a-string-of-varying-length – jpw Sep 07 '16 at 14:20
  • could be from 1 to 4 numbers separated by dots. It's using SQL Server. – Beth B Sep 07 '16 at 14:26
  • 1
    `SELECT * FROM your_table ORDER BY CAST('/' + REPLACE(sequentialOrder , '.', '/') + '/' AS HIERARCHYID);` This is the second answer to the question I linked to adapted for you. If it works for you, consider marking your question as a duplicate of the linked one. – jpw Sep 07 '16 at 14:39
  • Yes this hierarchyid works perfectly. Thank you so much. Does it risk being much slower or intensive than the usual ordering by a text field? – Beth B Sep 07 '16 at 14:47
  • @BethB While using hierarchyid probably isn't the best option I would guess that using text functions to split the column wouldn't be optimal either. The best option might be to normalize your data and actually split the column in to multiple (one for each level). – jpw Sep 07 '16 at 19:07

1 Answers1

0

You have to parse the given version strings to access its components a implement its ORDER BY semantics. There are many ways to do it. I personally try to avoid CLR code, so here comes my suggestion: parse the version using the dot as separator, convert each value to number, aggregate it back into columns and use a proper ORDER BYclause:

-- this script uses the function [dbo].[DelimitedSplit8K] to split a delimited
-- string value into multiple rows; it was published and is avalable at
-- http://www.sqlservercentral.com/articles/Tally+Table/72993/

-- setup
CREATE TABLE version (id VARCHAR(20))
INSERT version (id) VALUES('1.2.4');
INSERT version (id) VALUES('2.3.8');
INSERT version (id) VALUES('11.3.4');

-- cte query
; WITH inrows AS (
  -- split version ID into many rows
  SELECT ver.id, dlm.itemNumber, CONVERT(INT, dlm.item) as item
  FROM version ver
  CROSS APPLY [dbo].[DelimitedSplit8K](ver.id, '.') dlm
)
, inCols AS (
  -- aggregate the rows groupped by id
  SELECT inr.id
  , MAX(CASE WHEN inr.itemNumber = 1 THEN inr.item ELSE NULL END) AS [major]
  , MAX(CASE WHEN inr.itemNumber = 2 THEN inr.item ELSE NULL END) AS [minor]
  , MAX(CASE WHEN inr.itemNumber = 3 THEN inr.item ELSE NULL END) AS [revision]
  FROM inrows inr
  GROUP BY inr.id
)
SELECT *
FROM inCols inc
ORDER BY inc.major, inc.minor, inc.revision;

-- RESULTS ================================================
-- id                   major       minor       revision
-- -------------------- ----------- ----------- -----------
-- 1.2.4                1           2           4
-- 2.3.8                2           3           8
-- 11.3.4               11          3           4
Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47