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 1234»»»

display order by like 1,2,3,4,5...............plz write quarie Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 2:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
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
Post #1511010
Posted Monday, November 4, 2013 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:33 AM
Points: 8, Visits: 24
Hi Sashi,

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


SELECT houseno
FROM test
order by houseno

Post #1511013
Posted Monday, November 4, 2013 2:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
Iam try this quarie but not display above format show as above
Post #1511016
Posted Monday, November 4, 2013 2:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 28, 2014 5:56 AM
Points: 632, Visits: 1,152
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
Post #1511018
Posted Monday, November 4, 2013 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:33 AM
Points: 8, Visits: 24
can you tell me your SQL server version ?
Datatype of this column nvarchar or varchar or something else ?

Regards,
Krishna
Post #1511021
Posted Monday, November 4, 2013 2:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
sql 2008 version ,,nvarchar(MAX)
Post #1511023
Posted Monday, November 4, 2013 7:30 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 @ 10:07 PM
Points: 3,617, Visits: 5,236
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!
Post #1511343
Posted Monday, November 4, 2013 10:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511359
Posted Monday, November 4, 2013 10:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511361
Posted Monday, November 4, 2013 10:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511362
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse