December 14, 2008 at 12:53 pm
want to order in a particular column having thr below data.
1
a1
d1
12
0
90
9800
.
i need the output 1 mast be the first alue than the rest values in sorted order
December 14, 2008 at 1:42 pm
ORDER BY RIGHT('00000'+MyColumn, n)
where n is the length of you column. Ensure that there are enough 0's.
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
December 14, 2008 at 1:45 pm
Something like this:
Select *
From yourTable
Order By (Case When col=1 then 0 else 1 End), Col1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 1:47 pm
Hmm, I think that we must have interpreted his post differently, Chris.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 2:17 am
rbarryyoung (12/14/2008)
Hmm, I think that we must have interpreted his post differently, Chris.
Heh - yeah, think so. I didn't read it properly first time. "1" first, followed by everything else including "0", in an unspecified sorted order.
Sanjay, can you list these items in the order in which you wish them to appear?
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
December 15, 2008 at 2:28 am
Hi! Chris Morris
Thanx for reply
region IdParentIdText
a11wwer
b11yrt
c11ryrt
a11a1eryrty
b11b1nbnvnfth
c11c1vc
b111b11vbc
a111a11cb
c111c11bc
result should be
a11wwer
b11yrt
c11ryrt
then all rest values sort order by parent id
The major issues is this that the parent id is generated by Java Random method as string and it can be any thing
but the first parent id is assign the manual
i hope you got the issues ,
waiting for ur reply
Regards,
Sanjay
December 15, 2008 at 2:57 am
Hi Sanjay
I'm still not totally sure of your requirement but try this - it will generate the sort order you're expecting from the given sample data.
DROP TABLE #Temp
CREATE TABLE #Temp (regionId VARCHAR(10), ParentId VARCHAR(10), TextColumn VARCHAR(10))
INSERT INTO #Temp (regionId, ParentId, TextColumn)
SELECT 'a1', '1', 'wwer' UNION ALL
SELECT 'b1', '1', 'yrt' UNION ALL
SELECT 'c1', '1', 'ryrt' UNION ALL
SELECT 'a11', 'a1', 'eryrty' UNION ALL
SELECT 'b11', 'b1', ' nbnvnfth' UNION ALL
SELECT 'c11', 'c1', 'vc' UNION ALL
SELECT 'b111', 'b11', 'vbc' UNION ALL
SELECT 'a111', 'a11', 'cb' UNION ALL
SELECT 'c111', 'c11', 'bc'
SELECT RIGHT('000000000' + regionId, 10), *
FROM #Temp
ORDER BY RIGHT('000000000' + regionId, 10), ParentId
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
December 15, 2008 at 6:53 am
This is Barry's original case method applied to your table structure.
SELECT *
FROM #Temp
ORDER BY (CASE WHEN ParentID = '1' THEN 0 ELSE 1 END), ParentId
Notice the way Chris posted your table structure and sample data. Please use this format in future posts, as it makes things much easier for us to deal with. For more instructions on how to do this, please see the link in either of our signatures. Thanks for the inserts Chris. 😎
December 15, 2008 at 6:56 am
'Welcome Seth 🙂
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply