Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sort by serialcode Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 12:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:47 AM
Points: 31, Visits: 377
i need the output to be sort by serial how can i sort this so after 1.3 , 1.5 shall come and then 1.10.
i think tally table can be used for this purpose.

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2','b' UNION ALL
SELECT '1.3','C' UNION ALL
SELECT '1.10','G' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E'

SELECT * FROM @Result ORDER BY Serial

thanks


Post #1527361
Posted Friday, January 3, 2014 1:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
sayedkhalid99 (1/3/2014)
... after 1.3 , 1.5 shall come and then 1.10....


1.10 usually sorts before 1.3. If this is the case, try casting to decimal:

SELECT * FROM @Result ORDER BY CAST(Serial AS DECIMAL(n,n))

If not, split the string on '.' and order by the first part, cast as INT, then the second part, cast as INT.



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527386
Posted Friday, January 3, 2014 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:47 AM
Points: 31, Visits: 377
thanks chris for your reply . i need a recursive way to deal with serial hierarchy.
the problem is the serial can be like this.

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1.3','E'
SELECT * FROM @Result ORDER BY serial
Post #1527480
Posted Friday, January 3, 2014 6:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
What's the maximum number of elements you can have in a "serial"? Your sample data shows 3, can it be more? There are two good ways of splitting this into columns, Jeff Moden's delimitedsplit8k and cascading CROSS APPLY. cCA is only good for up to 5 elements.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527488
Posted Friday, January 3, 2014 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:47 AM
Points: 31, Visits: 377
yeah jeff as already answered to my similar query in here .
http://www.sqlservercentral.com/Forums/Topic1194903-338-1.aspx#bm1196064
but i can not reproduce in this example.
i think 5 element will be enough .

i tried this from the example didn't worked, because it give same hierarchypath to multiple rows.
here is what i tried.

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1.3','E'


;WITH cte
AS
(
SELECT *,CAST(CAST(serial AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath
FROM @Result
)
,
cteSplit AS
(
SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS NewSerial,
h.title AS title,h.Serial
FROM dbo.Tally AS t WITH(NOLOCK)
CROSS JOIN cte AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4
)
SELECT title,serial,newserial FROM cteSplit ORDER BY newserial
GO


Post #1527552
Posted Friday, January 3, 2014 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3.4','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '1.2.33.4.5','Extra row' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1.3','E'

SELECT r.*

FROM @Result r

CROSS APPLY (SELECT n = CHARINDEX('.',Serial,0)) p1
CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p1.n+1),0)) p2
CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p2.n+1),0)) p3
CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p3.n+1),0)) p4
CROSS APPLY (
SELECT
Elem1 = LEFT(Serial, p1.n-1),
Elem2 = SUBSTRING(Serial, p1.n+1, ISNULL(p2.n-(p1.n+1),8000)),
Elem3 = SUBSTRING(Serial, p2.n+1, ISNULL(p3.n-(p2.n+1),8000)),
Elem4 = SUBSTRING(Serial, p3.n+1, ISNULL(p4.n-(p3.n+1),8000)),
Elem5 = SUBSTRING(Serial, p4.n+1, 8000)
) x

ORDER BY CAST(x.Elem1 AS INT), CAST(x.Elem2 AS INT), CAST(x.Elem3 AS INT), CAST(x.Elem4 AS INT), CAST(x.Elem5 AS INT)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527582
Posted Friday, January 3, 2014 9:07 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 3,122, Visits: 11,401
Here is another method using the PARSENAME function to split the elements when you have 4 or fewer elements.


-- Parse up to 4 '.' delimited elements and sort by numeric value of element
select
a1.x,
a1.y,
Part1 = convert(int,nullif(parsename(a1.y,4),'')),
Part2 = convert(int,nullif(parsename(a1.y,3),'')),
Part3 = convert(int,nullif(parsename(a1.y,2),'')),
Part4 = convert(int,nullif(parsename(a1.y,1),''))
from
(
select
a.x,
y =
case datalength(a.x)-datalength(replace(a.x,'.',''))
when 0 then a.x+'. . . '
when 1 then a.x+'. . '
when 2 then a.x+'. '
when 3 then a.x
else null end
from
(
select x = '10.12.14.44' union all
select x = '10.12.4.44' union all
select x = '1.20.3' union all
select x = '1.20.41' union all
select x = '1.12' union all
select x = '1.2' union all
select x = '1..3' union all
select x = '1..3.5' union all
select x = '5' union all
select x = '1'
) a
) a1
order by
Part1,
Part2,
Part3,
Part4

x           y                 Part1       Part2       Part3       Part4
----------- ----------------- ----------- ----------- ----------- -----------
1 1. . . 1 NULL NULL NULL
1..3 1..3. 1 NULL 3 NULL
1..3.5 1..3.5 1 NULL 3 5
1.2 1.2. . 1 2 NULL NULL
1.12 1.12. . 1 12 NULL NULL
1.20.3 1.20.3. 1 20 3 NULL
1.20.41 1.20.41. 1 20 41 NULL
5 5. . . 5 NULL NULL NULL
10.12.4.44 10.12.4.44 10 12 4 44
10.12.14.44 10.12.14.44 10 12 14 44




Post #1527601
Posted Friday, January 3, 2014 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:47 AM
Points: 31, Visits: 377
thank you very much chris for providing the solution.
Post #1527606
Posted Friday, January 3, 2014 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:47 AM
Points: 31, Visits: 377
thanks Michael Valentine Jones for you solution if you could find time please change your solution to example i provided above.
Post #1527620
Posted Friday, January 3, 2014 2:08 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 3,122, Visits: 11,401
sayedkhalid99 (1/3/2014)
thanks Michael Valentine Jones for you solution if you could find time please change your solution to example i provided above.


Is there some reason why you can't do that yourself?

Post #1527723
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse