January 3, 2014 at 12:24 am
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
January 3, 2014 at 1:54 am
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.
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
January 3, 2014 at 6:23 am
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
January 3, 2014 at 6:32 am
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.
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
January 3, 2014 at 7:59 am
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
January 3, 2014 at 8:45 am
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)
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
January 3, 2014 at 9:07 am
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
January 3, 2014 at 9:13 am
thank you very much chris for providing the solution.
January 3, 2014 at 9:28 am
thanks Michael Valentine Jones for you solution if you could find time please change your solution to example i provided above.
January 3, 2014 at 2:08 pm
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?
January 3, 2014 at 5:18 pm
The following will handle more levels than you can shake a stick at with numbers as large as 99999999. You'll need the DelimitedSplit8K function for this and I've included a link to that article after the code below.
--===== Build the test data
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'
;
--===== Solve the problem by creating a hierarchical sort path using a very high speed
-- method for the right alignment of integer data (thanks, Dwaine)
WITH
cteBuildPath AS
(
SELECT r.Serial
,r.Title
,SortPath =
(--==== Split the parts of each Serial and reassemble as a hierarchical path
SELECT RIGHT(split.Item+100000000,8) --Converts each # to a zero filled right aligned number of 8 digits.
FROM @Result r1
CROSS APPLY dbo.DelimitedSplit8K(Serial,'.') split
WHERE r1.Serial = r.Serial
FOR XML PATH('')
)
FROM @Result r
)
SELECT Serial,Title
FROM cteBuildPath
ORDER BY SortPath
;
The DelimitedSplit8K function may be found at the following URL...
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2014 at 9:50 pm
thanks jeff for your reply , i will check this
January 3, 2014 at 11:57 pm
ChrisM@Work (1/3/2014)
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.1','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)
what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.
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','E'
January 4, 2014 at 11:24 am
sayedkhalid99 (1/3/2014)
what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.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','E'
With absolutely no disrespect intended or implied towards anyones fine work on this thread, my recommendation would be to not do it in any way that has such a small limit on the number of levels because there will be a drop-everything panic if a 6th or 7th level ever shows up. Don't say it won't happen. Make your code bullet proof and scalable and just assume that it will happen when you can least afford it to.
As a bit of a sidebar, if it were me, I'd stop recaclulating things that won't change over and over again. I'd turn my code into a function and add a persisted computed column that contains the function to the table so that I don't ever have to worry about calculating a sort order on something that's almost perfectly static.
As a hidden benefit of using the code I posted, you'll also have the DelimitedSplit8K function, which you find dozens of other uses for.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 11:00 pm
thanks for your feed back jeff, since i already implemented the first solution provided when i received the code in my project just wanted to change that rather then new code due to shortage of time and deadlines, later on i decided to use your solution and worked perfectly.
i will take your suggestion on
Make your code bullet proof and scalable and just assume that it will happen when you can least afford it to.
And I'd stop recaclulating things that won't change over and over again.
thanks for your continuous support to community members.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply