How to set order by this method [Resolved]

Posted by Jayakumars under .NET Framework on 3/4/2016 | Points: 10 | Views : 1474 | Status : [Member] [MVP] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

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 Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

0	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 Kumaraspcode2009@gmail.com Jayakumars, 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

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

0	@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

Latest Posts