Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 display order by like 1,2,3,4,5...............plz write quarie Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, November 4, 2013 2:11 AM
 SSC Rookie Group: General Forum Members Last Login: Sunday, January 4, 2015 7:28 PM Points: 33, Visits: 131
 Examples of values I want to put in orderhouseno3-13-13-13-3/a3-13-3/b3-13-2/a3-13-43-13-63-13-5/a3-13-4/ci want output like this belowhouseno3-13-13-13-2/a3-13-3/a3-13-3/b3-13-43-13-4/c3-13-5/a3-13-6
Post #1511010
 Posted Monday, November 4, 2013 2:23 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, September 1, 2015 8:58 PM Points: 11, Visits: 32
 Hi Sashi,Please find below query for this.. this is so simple query..`SELECT housenoFROM testorder by houseno`
Post #1511013
 Posted Monday, November 4, 2013 2:26 AM
 SSC Rookie Group: General Forum Members Last Login: Sunday, January 4, 2015 7:28 PM Points: 33, Visits: 131
 Iam try this quarie but not display above format show as above
Post #1511016
 Posted Monday, November 4, 2013 2:31 AM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 6:32 AM Points: 1,342, Visits: 2,565
 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 @tblTempSelect '3-13-1' union allSelect '3-13-3/a' union allSelect '3-13-3/b' union allSelect '3-13-2/a' union allSelect '3-13-4' union allSelect '3-13-6' union allSelect '3-13-5/a' union allSelect '3-13-4/c'Select * from @tblTempOrder by REPLACE(houseno,'-','')`Hope it helps
Post #1511018
 Posted Monday, November 4, 2013 2:32 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, September 1, 2015 8:58 PM Points: 11, Visits: 32
 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 Group: General Forum Members Last Login: Sunday, January 4, 2015 7:28 PM Points: 33, Visits: 131
 sql 2008 version ,,nvarchar(MAX)
Post #1511023
 Posted Monday, November 4, 2013 7:30 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 housenoFROM SampleData aCROSS APPLY( SELECT FirstPart = 0+LEFT(houseno, CHARINDEX('-', houseno) - 1) ,SecondPart = SUBSTRING(houseno, CHARINDEX('-', houseno) + 1, 99)) bCROSS APPLY( SELECT SecondPart = 0+LEFT(SecondPart, CHARINDEX('-', SecondPart) - 1) ,ThirdPart = SUBSTRING(SecondPart, CHARINDEX('-', SecondPart) + 1, 99) ) cCROSS APPLY( SELECT ThirdPart = 0+LEFT(ThirdPart, CHARINDEX('/', ThirdPart + '/') - 1) ,FourthPart = CASE CHARINDEX('/', ThirdPart) WHEN 0 THEN '' ELSE SUBSTRING(ThirdPart, CHARINDEX('/', ThirdPart) + 1, 99) END) dORDER 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 housenoFROM SampleData aCROSS APPLY DelimitedSplit8K(houseno, '-') bCROSS 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) cGROUP BY housenoORDER 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
Post #1511343
 Posted Monday, November 4, 2013 10:33 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1511359
 Posted Monday, November 4, 2013 10:42 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1511361
 Posted Monday, November 4, 2013 10:55 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 shashianireddy (11/4/2013)Iam try this quarie but not display above format show as aboveFor 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 @tblTempSelect '3-13-1' union allSelect '3-13-3/a' union allSelect '3-13-3/b' union allSelect '3-13-2/a' union allSelect '3-13-4' union allSelect '3-13-6' union allSelect '3-13-5/a' union allSelect '3-13-4/c'; SELECT HouseNo FROM @tblTemp ORDER BY HouseNo;`Results from above...`3-13-13-13-2/a3-13-3/a3-13-3/b3-13-43-13-4/c3-13-5/a3-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." Helpful Links:How to post code problemsHow to post performance problems
Post #1511362

 Permissions