How to set order by this method [Resolved]

Posted by Jayakumars under .NET Framework on 3/4/2016 | Points: 10 | Views : 259 | Status : [Member] [MVP] | Replies : 5
create table tttt
(
Eid int,
ECODE varchar(40)
)


insert into tttt values(1,'AAAA/00001')
insert into tttt values(2,'BBB/00010')
insert into tttt values(3,'CCC/00002')
insert into tttt values(4,'ZZZ/00100')


-- But i need order by based on ECODE

--like this
--AAAA/00001
--BBB/00002
--CCC/00010
--ZZZ/00100

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 3/5/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try this


Declare @t Table( Id int, Code varchar(20) )

insert into @t values(1,'AAAA/00001')
insert into @t values(2,'BBB/00010')
insert into @t values(3,'CCC/00002')
insert into @t values(4,'ZZZ/00100')

;With Cte AS(
Select
x.Id
,LeftPart =PARSENAME(REPLACE(Code,'/','.'),2)
,RightPart=PARSENAME(REPLACE(Code,'/','.'),1)
,Rn=ROW_NUMBER() OVER(ORDER BY PARSENAME(REPLACE(Code,'/','.'),1))
From @t x)
Select
l.Id
,ECode = Concat(l.LeftPart,'/',r.RightPart)
From (Select c.Id,c.LeftPart From Cte c) l
Join (Select c.Rn,c.RightPart From Cte c) r ON l.Id = r.Rn




Result
Eid	ECode

1 AAAA/00001
2 BBB/00002
3 CCC/00010
4 ZZZ/00100


Hope this helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Professionaluser on: 3/4/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
you have to use direct ORDER BY clause to sort the results

SELECT * FROM tttt ORDER BY ECODE

revert me back if you have any other requirement than the above?

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/4/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

i am using already this


but shows wrong output . Records come like this also need sno this query Professionaluser


1 AAAA/00001
2 BBB/00010
3 CCC/00002
4 ZZZ/00100



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Professionaluser on: 3/7/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi Jayakumar,

If you want to sort the results by number series in the ECODE column, then use below simple query.
Declare @t Table( Id int, Code varchar(20) )

insert into @t values(1,'AAAA/00001')

insert into @t values(2,'BBB/00010')

insert into @t values(3,'CCC/00002')

insert into @t values(4,'ZZZ/00100')

SELECT *, RIGHT(code, charindex('/',code)) SortPart FROM @t
ORDER BY RIGHT(code, charindex('/',code))

/*
Id Code SortPart
1 AAAA/00001 00001
3 CCC/00002 0002
2 BBB/00010 0010
4 ZZZ/00100 0100
*/



@Rajnilari2015,
Your query retunrs the results by changing the ECODE values ( see the difference below)
Your query output:
Id	ECode

1 AAAA/00001
2 BBB/00002
3 CCC/00010
4 ZZZ/00100


my query result
Id	Code

1 AAAA/00001
3 CCC/00002
2 BBB/00010
4 ZZZ/00100


Jayakumar, did you observe the difference here? which result you wanted ?

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 3/7/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Professionaluser Sir, please re-read the question again that will clarify the doubt. (:

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response