How to Order for this Records in sql server [Resolved]

@JayaKumar,
Try this

DECLARE @T TABLE(Record VARCHAR(20))



INSERT INTO @T 



SELECT '01A1001' UNION ALL 

SELECT '02A1001' UNION ALL

SELECT '03A1001' UNION ALL

SELECT '04A1001' UNION ALL

SELECT '15A1001' UNION ALL 

SELECT '18A1001' UNION ALL 

SELECT '20A1001' UNION ALL

SELECT '17A1001' UNION ALL 

SELECT '12A1001' UNION ALL

SELECT '13A1001' UNION ALL

SELECT '14A1001' UNION ALL

SELECT '16A1001' UNION ALL 

SELECT '05A1001' UNION ALL

SELECT '06A1001' UNION ALL

SELECT '07A1001' UNION ALL

SELECT '08A1001' UNION ALL

SELECT '09A1001' UNION ALL

SELECT '10A1001' UNION ALL

SELECT '11A1001' UNION ALL

SELECT '19A1001' UNION ALL 

SELECT '01B1001' UNION ALL 

SELECT '02B1001' UNION ALL

SELECT '03B1001' UNION ALL

SELECT '04B1001' UNION ALL

SELECT '15B1001' UNION ALL 

SELECT '10B1001' UNION ALL

SELECT '20B1001' UNION ALL

SELECT '12B1001' UNION ALL

SELECT '14B1001' UNION ALL

SELECT '13B1001' UNION ALL

SELECT '05B1001' UNION ALL

SELECT '06B1001' UNION ALL

SELECT '17B1001' UNION ALL 

SELECT '07B1001' UNION ALL

SELECT '08B1001' UNION ALL

SELECT '18B1001' UNION ALL

SELECT '16B1001' UNION ALL 

SELECT '09B1001' UNION ALL

SELECT '11B1001' UNION ALL

SELECT '19B1001'



SELECT *

FROM @T

ORDER BY

CAST(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 1) AS INT) DESC

,LEFT(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)),1) 

Result
--------

Record

20A1001

20B1001

19A1001

19B1001

18A1001

18B1001

17A1001

17B1001

16A1001

16B1001

15A1001

15B1001

14A1001

14B1001

13A1001

13B1001

12A1001

12B1001

11A1001

11B1001

10A1001

10B1001

09A1001

09B1001

08A1001

08B1001

07A1001

07B1001

06A1001

06B1001

05A1001

05B1001

04A1001

04B1001

03A1001

03B1001

02A1001

02B1001

01A1001

01B1001

--
Thanks & Regards,
RNA Team

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

Hi
Thanks for good reply . But I need order desc like this

20A1001
20B1001
19A1001
19B1001

check above last record in table 20 serial

so i need
20A
20B
after
19A
19B

i tried like this

SELECT *
FROM @T ORDER BY Record DESC

but wrong out given like this

20B1001
20A1001
19B1001
19A1001


I need this output

20A
20B
after
19A
19B
etc
01A1001
01B1001


I am waiting for your reply for best answer.
can you reply soon.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Hi

I try and let u know

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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