none
How to sort row using decimal point like this (1.1.12) RRS feed

  • Question

  • Hi

    just want to ask if how can i sort my rows using the ID but the ID is a varchar like this (1.1.1, 1.1.2, 1.1.3 up to 2.2.10)

    please advise.

    here is my whole code and the column id is named "RPS ID"

    WITH CTE AS
    (
    	SELECT  RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title as RPS
    	,		QMSManual.ManualType + ' ' + QMSManual.QMSID + ' ' + QMSManual.QMS_Title as QMS
    	,		RPS_Reference.RPS_ID RPS_ID
    	,		QMSManual.QMSID QMSID
    	,		QMSManual.QMS_Link QMSLink
    	,		QMSManual.LinkTitle LinkTitle
    	,		QMSManual.ComplianceLink ComplianceLink
    	,		ROW_NUMBER() OVER(PARTITION BY RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title ORDER BY QMSManual.QMSID) AS Ranking
    	FROM        QMSManual 
    	right JOIN	RPS_Reference 
    	ON QMSManual.RPS_ID = RPS_Reference.RPS_ID
    )
    SELECT	CASE 
    			WHEN Ranking = 1 THEN RPS
    			ELSE '---'
    		END AS RPS
    ,		QMS
    ,		RPS_ID	
    ,		QMSID	
    ,		QMSLink
    ,		LinkTitle
    ,		ComplianceLink
    FROM	CTE
    ORDER	BY	3, 2

    Wednesday, July 25, 2012 6:49 AM

Answers

  • I think you can try PARSENAME
     
    Something like
    PARSENAME(ID, 3) -- returns the first digit
    PARSENAME(ID, 2) -- returns the second digit
    CAST(PARSENAME(ID, 1) AS INT)  -- returns th third number and makes sure
    that 10 is after 2,3 ...
     

    Fred
    Wednesday, July 25, 2012 11:40 AM
  • I use the decimal outline format in my books. I pad each level with leading zeroes and pull them out in the presentation layer. 

    NSERT INTO @TempTable
    ('01.01.01'),
    ('01.01.02'),
    ('01.01.03'),
    ('01.01.04'),
    ('02.02.05'),
    ('02.02.06'),
    ('02.02.07'),
    ('02.02.08'),
    ('02.02.09'),
    ('02.02.10');


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 25, 2012 2:45 PM
  • WITH CTE AS
    (
    	SELECT  RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title as RPS
    	,		QMSManual.ManualType + ' ' + QMSManual.QMSID + ' ' + QMSManual.QMS_Title as QMS
    	,		RPS_Reference.RPS_ID RPS_ID
    	,		QMSManual.QMSID QMSID
    	,		QMSManual.QMS_Link QMSLink
    	,		QMSManual.LinkTitle LinkTitle
    	,		QMSManual.ComplianceLink ComplianceLink
    	,		ROW_NUMBER() OVER(PARTITION BY RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title ORDER BY QMSManual.QMSID) AS Ranking
    	FROM        QMSManual 
    	right JOIN	RPS_Reference 
    	ON QMSManual.RPS_ID = RPS_Reference.RPS_ID
    )
    SELECT	CASE 
    			WHEN Ranking = 1 THEN RPS
    			ELSE '---'
    		END AS RPS
    ,		QMS
    ,		RPS_ID	
    ,		QMSID	
    ,		QMSLink
    ,		LinkTitle
    ,		ComplianceLink
    FROM	CTE
    ORDER	BY	Cast('/' + RPS_ID + '/' As hierarchyid), QMS


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Wednesday, July 25, 2012 8:00 AM

All replies

  • You can create replace the '.' with '' and cast it as integer and then try sort it.

    Try something like below.

    DECLARE @TempTable TABLE
    (
        Id varchar(10)
    )
    
    INSERT INTO @TempTable
    SELECT '1.1.1' UNION ALL
    SELECT '1.1.2' UNION ALL
    SELECT '1.1.3' UNION ALL
    SELECT '1.1.4' UNION ALL
    SELECT '2.2.5' UNION ALL
    SELECT '2.2.6' UNION ALL
    SELECT '2.2.7' UNION ALL
    SELECT '2.2.8' UNION ALL
    SELECT '2.2.9' UNION ALL
    SELECT '2.2.10'
    
    ;WITH CTE1(Id,IntId)
    as
    (
        select Id,cast(replace(Id,'.','') as int) as IntId from @TempTable
    )
    select * from CTE1 order by IntId desc

    Let me know if it is worked.

    Best Regards, Venkat

    Wednesday, July 25, 2012 7:23 AM
  • hi i cant understand, can you implement that on my code above please? thanks in advance
    Wednesday, July 25, 2012 7:28 AM
  • I assume RPS_Reference.RPS_ID is varchar type and its value like 1.1.1, 1.1.2, 1.1.3....

    Please check the below and let me know if that works

    WITH CTE AS
    (
    	SELECT  RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title as RPS
    	,		QMSManual.ManualType + ' ' + QMSManual.QMSID + ' ' + QMSManual.QMS_Title as QMS
    	,		RPS_Reference.RPS_ID RPS_ID
    	,		QMSManual.QMSID QMSID
    	,		QMSManual.QMS_Link QMSLink
    	,		QMSManual.LinkTitle LinkTitle
    	,		QMSManual.ComplianceLink ComplianceLink
    	,		ROW_NUMBER() OVER(PARTITION BY RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title ORDER BY QMSManual.QMSID) AS Ranking
    	,		cast(replace(RPS_Reference.RPS_ID,'.','') as int) as RPS_Reference.RPS_IntId
    
    	FROM        QMSManual 
    	right JOIN	RPS_Reference 
    	ON QMSManual.RPS_ID = RPS_Reference.RPS_ID
    )
    SELECT	CASE 
    			WHEN Ranking = 1 THEN RPS
    			ELSE '---'
    		END AS RPS
    ,		QMS
    ,		RPS_ID	
    ,		QMSID	
    ,		QMSLink
    ,		LinkTitle
    ,		ComplianceLink
    FROM	CTE
    ORDER	BY	RPS_IntId


    Best Regards, Venkat


    • Edited by Venkats Wednesday, July 25, 2012 7:34 AM
    Wednesday, July 25, 2012 7:34 AM
  • I would either split the numbers into separate columns or create a number from the string. e.g. 1.1.1 --> 1001001, 1.1.2 --> 1001002, 2.2.10 --> 2002010.

    Both require parsing of the string, and that not is something easy to do in T-SQL. I would probably consider writing a CLR function in C# to do this.

    Wednesday, July 25, 2012 7:37 AM
  • WITH CTE AS
    (
    	SELECT  RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title as RPS
    	,		QMSManual.ManualType + ' ' + QMSManual.QMSID + ' ' + QMSManual.QMS_Title as QMS
    	,		RPS_Reference.RPS_ID RPS_ID
    	,		QMSManual.QMSID QMSID
    	,		QMSManual.QMS_Link QMSLink
    	,		QMSManual.LinkTitle LinkTitle
    	,		QMSManual.ComplianceLink ComplianceLink
    	,		ROW_NUMBER() OVER(PARTITION BY RPS_Reference.RPS_ID + ' ' + RPS_Reference.RPS_Title ORDER BY QMSManual.QMSID) AS Ranking
    	FROM        QMSManual 
    	right JOIN	RPS_Reference 
    	ON QMSManual.RPS_ID = RPS_Reference.RPS_ID
    )
    SELECT	CASE 
    			WHEN Ranking = 1 THEN RPS
    			ELSE '---'
    		END AS RPS
    ,		QMS
    ,		RPS_ID	
    ,		QMSID	
    ,		QMSLink
    ,		LinkTitle
    ,		ComplianceLink
    FROM	CTE
    ORDER	BY	Cast('/' + RPS_ID + '/' As hierarchyid), QMS


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Wednesday, July 25, 2012 8:00 AM
  • I prefer Stevens' method using hierarchyid, however this works in SQL Server 2008 and later. If you frequently sorting data based on RPS_ID (assuming this is the column) then I suggest you to create three persisted computed columns  in the table that splits the RPS_ID to three distinct values, like 1.2.10 becomes separate column values in three columns:

    ALTER TABLE RPS_Reference ADD
    FirstID AS CAST(LEFT(RPS_ID, (CHARINDEX('.', RPS_ID)) - 1) AS INT) PERSISTED
    ALTER TABLE RPS_Reference ADD
    SecondID AS CAST(SUBSTRING(RPS_ID, (CHARINDEX('.', RPS_ID)) + 1, 
    	((CHARINDEX('.', RPS_ID, CHARINDEX('.', RPS_ID) + 1)) - (CHARINDEX('.', RPS_ID)) - 1)) AS INT) PERSISTED
    	
    ALTER TABLE RPS_Reference ADD
    ThirdID AS CAST(RIGHT(RPS_ID, (LEN(RPS_ID) - (CHARINDEX('.', ColumnA, CHARINDEX('.', RPS_ID) + 1)))) AS INT) PERSISTED

    Then you can easly sort the query as below:

    SELECT 
    	RPS_ID,
    	--- other column list
    FROM 
    	RPS_Reference ORDER BY FirstID, SecondID, ThirdID


    Krishnakumar S

    Wednesday, July 25, 2012 8:31 AM
  • To create a number from string I would create a file called Sort.cs in let's say c:\tmp:

    using System;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    static public class Sort
    {
        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        static public SqlInt64 StringToNumber(SqlString input)
        {
            SqlInt64 Result = 0;
            string[] a = input.Value.Split(new char[] { '.' });        
            int Length = a.Length;
    
            for (int i = 0; i < Length; i++)
            {
                Result += Convert.ToInt64(a[i]) * Convert.ToInt64(Math.Pow(1000, Length - i - 1)); 
            }
    
            return Result;
        }
    }

    Then I would compile this source code into a DLL. Open a Command Prompt and execute the following command:

    c:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library /out:c:\tmp\Sort.dll c:\tmp\Sort.cs

    After that I import this DLL into the database:

    USE test;
    
    GO
    
    CREATE ASSEMBLY Sort from 'c:\tmp\Sort.dll';


    Following that I would create the CLR function so I call the function from T-SQL:

    CREATE FUNCTION dbo.StringToNumber
    (
    	@input nvarchar(max)
    )
    RETURNS BIGINT
    AS EXTERNAL name Sort."Sort".StringToNumber;

    And I would enable CLR if this wasn't already done:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO

    Now the CLR function is ready to be called. First a simple test. The output should be 1001001.

    SELECT dbo.StringToNumber(N'1.1.1');

    If this works we can now sort using the new CLR function:

    DECLARE @TempTable TABLE
    (
        Id varchar(10)
    );
    
    INSERT INTO @TempTable VALUES 
      ('1.1.1') 
    , ('2.2.10')
    , ('1.1.3')
    , ('2.2.8')
    , ('1.1.4')
    , ('2.2.7')
    , ('2.2.6')
    , ('2.2.5')
    , ('2.2.9')
    , ('1.1.2');
    
    SELECT *
    FROM @TempTable
    ORDER BY dbo.StringToNumber(Id);

    • Edited by Arjen Krap Wednesday, July 25, 2012 8:44 AM Minor formating tweaks
    Wednesday, July 25, 2012 8:39 AM
  • I think you can try PARSENAME
     
    Something like
    PARSENAME(ID, 3) -- returns the first digit
    PARSENAME(ID, 2) -- returns the second digit
    CAST(PARSENAME(ID, 1) AS INT)  -- returns th third number and makes sure
    that 10 is after 2,3 ...
     

    Fred
    Wednesday, July 25, 2012 11:40 AM
  • I use the decimal outline format in my books. I pad each level with leading zeroes and pull them out in the presentation layer. 

    NSERT INTO @TempTable
    ('01.01.01'),
    ('01.01.02'),
    ('01.01.03'),
    ('01.01.04'),
    ('02.02.05'),
    ('02.02.06'),
    ('02.02.07'),
    ('02.02.08'),
    ('02.02.09'),
    ('02.02.10');


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 25, 2012 2:45 PM
  • It didn't know about PARSENAME. Thanks for the tip! This will work if you have 4 levels or less. It's sure is simpler than my CLR solution, though that will work with more than 4 levels.

    An example using PARSENAME and 0 pre padding will look like this:

    DECLARE @TempTable TABLE ( Id varchar(10) );

    INSERT INTO @TempTable VALUES ('1.1.1') , ('2.2.10') , ('1.1.3') , ('2.2.8') , ('1.1.4') , ('2.2.7') , ('2.2.6') , ('2.2.5') , ('2.2.9') , ('1.1.2');

    SELECT * FROM @TempTable ORDER BY RIGHT(N'000' + PARSENAME(Id, 4), 3) , RIGHT(N'000' + PARSENAME(Id, 3), 3) , RIGHT(N'000' + PARSENAME(Id, 2), 3) , RIGHT(N'000' + PARSENAME(Id, 1), 3);

    This will however scan the table. No luck yet getting indexes to work. So you can only use it on small query results. For large tables you will have use another column to filter on.


    • Edited by Arjen Krap Wednesday, July 25, 2012 6:55 PM Fixed typo
    Wednesday, July 25, 2012 6:48 PM