SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


display order by like 1,2,3,4,5...............plz write quarie


display order by like 1,2,3,4,5...............plz write quarie

Author
Message
shashianireddy
shashianireddy
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 135
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
krishnaoptif
krishnaoptif
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 32
Hi Sashi,

Please find below query for this.. this is so simple query..



SELECT houseno
FROM test
order by houseno


shashianireddy
shashianireddy
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 135
Iam try this quarie but not display above format show as above
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5228 Visits: 2692
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
krishnaoptif
krishnaoptif
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 32
can you tell me your SQL server version ?
Datatype of this column nvarchar or varchar or something else ?

Regards,
Krishna
shashianireddy
shashianireddy
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 135
sql 2008 version ,,nvarchar(MAX)
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17371 Visits: 6431
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, 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210283 Visits: 41973
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210283 Visits: 41973
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210283 Visits: 41973
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search