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

Posted by Jayakumars under Sql Server on 10/15/2016 | Points: 10 | Views : 510 | Status : [Member] [MVP] | Replies : 3
Hi

01A1001
etc
20A1001
etc


---now i need ascending for
20A serial
20B serial
after
01A serial
01B Serial


How to asceding this type of records ascending

Mark as Answer if its helpful to you


Responses

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

Up
0
Down

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

Posted by: JAYAKUMARS on: 10/17/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

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

Posted by: Jayakumars on: 10/18/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

I try and let u know

Mark as Answer if its helpful to you

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

Login to post response