# 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

#### Responses

Posted by: Rajnilari2015 on: 3/5/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50
 0 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 TeamJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Professionaluser on: 3/4/2016 [Member] [MVP] Bronze | Points: 25
 0 you have to use direct ORDER BY clause to sort the resultsSELECT * FROM tttt ORDER BY ECODErevert 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
 0 hii am using already thisbut shows wrong output . Records come like this also need sno this query Professionaluser 1 AAAA/000012 BBB/000103 CCC/000024 ZZZ/00100Mark as Answer if its helpful to youJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Professionaluser on: 3/7/2016 [Member] [MVP] Bronze | Points: 25
 0 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