November 4, 2013 at 2:11 am
Examples of values I want to put in order
houseno
3-13-1
3-13-3/a
3-13-3/b
3-13-2/a
3-13-4
3-13-6
3-13-5/a
3-13-4/c
i want output like this below
houseno
3-13-1
3-13-2/a
3-13-3/a
3-13-3/b
3-13-4
3-13-4/c
3-13-5/a
3-13-6
November 4, 2013 at 2:23 am
Hi Sashi,
Please find below query for this.. this is so simple query..
SELECT houseno
FROM test
order by houseno
November 4, 2013 at 2:26 am
Iam try this quarie but not display above format show as above
November 4, 2013 at 2:31 am
Better practice is to maintain a sequence column in the table.
Anyways following is the query ....
Declare @tblTemp table ( ID int identity(1,1)
, houseno varchar(25)
)
Insert into @tblTemp
Select '3-13-1' union all
Select '3-13-3/a' union all
Select '3-13-3/b' union all
Select '3-13-2/a' union all
Select '3-13-4' union all
Select '3-13-6' union all
Select '3-13-5/a' union all
Select '3-13-4/c'
Select * from @tblTemp
Order by REPLACE(houseno,'-','')
Hope it helps
November 4, 2013 at 2:32 am
can you tell me your SQL server version ?
Datatype of this column nvarchar or varchar or something else ?
Regards,
Krishna
November 4, 2013 at 2:47 am
sql 2008 version ,,nvarchar(MAX)
November 4, 2013 at 7:30 pm
Here are 2 ways that are essentially identical:
WITH SampleData (houseno) AS
(
SELECT '3-13-1'
UNION ALL SELECT '3-13-3/a'
UNION ALL SELECT '3-13-3/b'
UNION ALL SELECT '3-13-2/a'
UNION ALL SELECT '3-13-4'
UNION ALL SELECT '3-13-6'
UNION ALL SELECT '3-13-5/a'
UNION ALL SELECT '3-13-4/c'
)
SELECT houseno
FROM SampleData a
CROSS APPLY
(
SELECT FirstPart = 0+LEFT(houseno, CHARINDEX('-', houseno) - 1)
,SecondPart = SUBSTRING(houseno, CHARINDEX('-', houseno) + 1, 99)
) b
CROSS APPLY
(
SELECT SecondPart = 0+LEFT(SecondPart, CHARINDEX('-', SecondPart) - 1)
,ThirdPart = SUBSTRING(SecondPart, CHARINDEX('-', SecondPart) + 1, 99)
) c
CROSS APPLY
(
SELECT ThirdPart = 0+LEFT(ThirdPart, CHARINDEX('/', ThirdPart + '/') - 1)
,FourthPart = CASE CHARINDEX('/', ThirdPart) WHEN 0 THEN ''
ELSE SUBSTRING(ThirdPart, CHARINDEX('/', ThirdPart) + 1, 99) END
) d
ORDER BY b.FirstPart, c.SecondPart, d.ThirdPart, d.FourthPart;
WITH SampleData (houseno) AS
(
SELECT '3-13-1'
UNION ALL SELECT '3-13-3/a'
UNION ALL SELECT '3-13-3/b'
UNION ALL SELECT '3-13-2/a'
UNION ALL SELECT '3-13-4'
UNION ALL SELECT '3-13-6'
UNION ALL SELECT '3-13-5/a'
UNION ALL SELECT '3-13-4/c'
)
SELECT houseno
FROM SampleData a
CROSS APPLY DelimitedSplit8K(houseno, '-') b
CROSS APPLY
(
SELECT item1=CASE WHEN CHARINDEX('/', item) = 0 THEN Item ELSE LEFT(item, CHARINDEX('/', item) - 1) END
,item2=CASE WHEN CHARINDEX('/', item) = 0 THEN '' ELSE SUBSTRING(item, CHARINDEX('/', item) + 1, 99) END
) c
GROUP BY houseno
ORDER BY
MAX(CASE WHEN ItemNumber = 1 THEN CAST(Item AS INT) END)
,MAX(CASE WHEN ItemNumber = 2 THEN CAST(Item AS INT) END)
,MAX(CASE WHEN ItemNumber = 3 THEN CAST(Item1 AS INT) END)
,MAX(CASE WHEN ItemNumber = 3 THEN Item2 END)
The DelimitedSplit8K FUNCTION can be found here[/url], however take care if you use this approach because DelimitedSplit8K is designed to be used with VARCHAR(8000) data types. So any NVARCHAR(MAX) address that is greater than 4000 bytes may get truncated, although I fail to see why you need 2GB to store a house number.
The first method uses cascading CROSS APPLYs to split out he pieces of the house number one at a time, casting each (except the last) so as to sort the house numbers as if they are integers.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 4, 2013 at 10:33 pm
twin.devil (11/4/2013)
Better practice is to maintain a sequence column in the table.
Why? I've added such a thing only when there is no other means and there is a means in this query. You just used one. 😉 A sequence column would require maintenance after every insert.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 10:42 pm
shashianireddy (11/4/2013)
sql 2008 version ,,nvarchar(MAX)
You're using NVARCHAR(MAX) for a housenumber???? In SQL Server 2008???? Why?????
You probably don't realize it but you cannot rebuild a clustered index in an Online fashion in SQL Server 2008 if the table contains a blob. This is a totally unneccessary blob.
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.
I strongly recommend that someone go fix that table to have the correctly sized datatypes.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 10:55 pm
shashianireddy (11/4/2013)
Iam try this quarie but not display above format show as above
For the given data, it certainly does. Please post the data you're using where it doesn't.
Declare @tblTemp table ( ID int identity(1,1)
, houseno NVARCHAR(MAX)
)
Insert into @tblTemp
Select '3-13-1' union all
Select '3-13-3/a' union all
Select '3-13-3/b' union all
Select '3-13-2/a' union all
Select '3-13-4' union all
Select '3-13-6' union all
Select '3-13-5/a' union all
Select '3-13-4/c'
;
SELECT HouseNo
FROM @tblTemp
ORDER BY HouseNo
;
Results from above...
3-13-1
3-13-2/a
3-13-3/a
3-13-3/b
3-13-4
3-13-4/c
3-13-5/a
3-13-6
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 11:00 pm
Jeff Moden (11/4/2013)
shashianireddy (11/4/2013)
sql 2008 version ,,nvarchar(MAX)You're using NVARCHAR(MAX) for a housenumber???? In SQL Server 2008???? Why?????
You probably don't release it but you cannot rebuild a clustered index in an Online fashion in SQL Server 2008 if the table contains a blob. This is a totally unneccessary blob.
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.
I strongly recommend that someone go fix that table to have the correctly sized datatypes.
Glad to see I'm not the only one that felt that way. Although your feelings seem a bit stronger on the subject. Probably because you're not seeing such nonsense as frequently as I do.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 4, 2013 at 11:09 pm
dwain.c (11/4/2013)
Jeff Moden (11/4/2013)
shashianireddy (11/4/2013)
sql 2008 version ,,nvarchar(MAX)You're using NVARCHAR(MAX) for a housenumber???? In SQL Server 2008???? Why?????
You probably don't release it but you cannot rebuild a clustered index in an Online fashion in SQL Server 2008 if the table contains a blob. This is a totally unneccessary blob.
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.
I strongly recommend that someone go fix that table to have the correctly sized datatypes.
Glad to see I'm not the only one that felt that way. Although your feelings seem a bit stronger on the subject. Probably because you're not seeing such nonsense as frequently as I do.
The reason why I don't see such nonsense that often is because I've trained my Developers. I would never allow such a table design to even go into Dev never mind Prod. This is the kind of garbage that some of the automatic front-end designer software does. What really kills me is that one of the products that does it is Microsoft software.
I'm all for the avoidance of prematue optimization but any system or human that creates all NVARCHAR(MAX) or even all NVARCHAR(4000) for all character based columns in a table is way over the top so far as I'm concerned.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 11:24 pm
Jeff Moden (11/4/2013)
dwain.c (11/4/2013)
Jeff Moden (11/4/2013)
shashianireddy (11/4/2013)
sql 2008 version ,,nvarchar(MAX)You're using NVARCHAR(MAX) for a housenumber???? In SQL Server 2008???? Why?????
You probably don't release it but you cannot rebuild a clustered index in an Online fashion in SQL Server 2008 if the table contains a blob. This is a totally unneccessary blob.
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.
I strongly recommend that someone go fix that table to have the correctly sized datatypes.
Glad to see I'm not the only one that felt that way. Although your feelings seem a bit stronger on the subject. Probably because you're not seeing such nonsense as frequently as I do.
The reason why I don't see such nonsense that often is because I've trained my Developers. I would never allow such a table design to even go into Dev never mind Prod. This is the kind of garbage that some of the automatic front-end designer software does. What really kills me is that one of the products that does it is Microsoft software.
I'm all for the avoidance of prematue optimization but any system or human that creates all NVARCHAR(MAX) or even all NVARCHAR(4000) for all character based columns in a table is way over the top so far as I'm concerned.
Sounds like you have the luxury of an orderly, controlled shop. Congratulations for exercising such strict controls (and being able to)!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 4, 2013 at 11:35 pm
Jeff Moden (11/4/2013)
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.
Premature optimisation IS the root of all evil. This is not premature optimisation. This is poor data type choice. Premature optimisation would be something like selecting char over varchar because someone heard it's faster and didn't do any evaluations to see if it is or if there's a performance problem in the first place.
Premature optimisation is making weird data type or design choices because 'they're faster' without any testing or any consideration as to whether any imagined or real performance problems exist, resulting in a more complex or strange design that's harder to maintain or even slower than it could be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2013 at 12:03 am
GilaMonster (11/4/2013)
Jeff Moden (11/4/2013)
Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is no one ever considers such things as what I've just mentioned and even fewer go back and optimize when they're supposed to.Premature optimisation IS the root of all evil. This is not premature optimisation. This is poor data type choice. Premature optimisation would be something like selecting char over varchar because someone heard it's faster and didn't do any evaluations to see if it is or if there's a performance problem in the first place.
Premature optimisation is making weird data type or design choices because 'they're faster' without any testing or any consideration as to whether any imagined or real performance problems exist, resulting in a more complex or strange design that's harder to maintain or even slower than it could be.
I'm well aware of that. You and I have had this conversation before and I absolutely agree. I should have said "and that someone is wrong because making good choices as to data type is NOT a pre-optimization."
My biggest problem with Mr. Knuth's fine words isn't with the words. It's with how people have tainted the meaning of the words. I had an "expert" "developer" look me straight in the face when I asked him why he designed a table with all of the character-based columns (even a Zip Code column!) as NVARCHAR(4000) and tell me that "Pre-Optimisation is the root of all evil". If I were a lesser man, I'd have killed him on the spot to end that particular gene pool. 😉
I have the same problem with such "expert" "developers" that think that Agile methods mean that you don't have to document your code. :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply