Geek Answers Handbook

Sort by last 2 characters

This is the result of my query, but it is not ordered correctly. I want to order by the last 2 characters. The result should be: Fa0/10 below Fa0/9 .

 Fa0/1 Fa0/10 Fa0/11 Fa0/12 Fa0/2 Fa0/3 Fa0/4 Fa0/5 Fa0/6 Fa0/7 Fa0/8 Fa0/9 Gi0/1 Gi0/2 Null0 Vlan1

My request:

 SELECT inft.port FROM interfaces AS intf ORDER BY RIGHT(intf.port + 0, 2)

Second: sqlfiddle

+7
source share
4 answers

Try the following:

 SELECT port FROM interfaces ORDER BY SUBSTRING_INDEX(port, '/', 1), CAST(SUBSTRING_INDEX(port, '/', -1) AS SIGNED)

Check SQL FIDDLE DEMO

OUTPUT

 | PORT | |--------| | Fa0/1 | | Fa0/2 | | Fa0/3 | | Fa0/4 | | Fa0/5 | | Fa0/6 | | Fa0/7 | | Fa0/8 | | Fa0/9 | | Fa0/10 | | Fa0/11 | | Fa0/12 | | Gi0/1 | | Gi0/2 | | Null0 | | Vlan1 |
+7
source

Why do you need + 0 ? Just delete it and it will work.

 SELECT port FROM interfaces ORDER BY RIGHT(port, 2)

SQL Fiddle Demo

Output:

 PORT ------------ Fa0/1 Gi0/1 Gi0/2 Fa0/2 Fa0/3 Fa0/4 Fa0/5 Fa0/6 Fa0/7 Fa0/8 Fa0/9 Fa0/10 Fa0/11 Fa0/12 Null0 Vlan1
+6
source

The order is correct - lexicographically.

You need to convert them to a number.

+1
source

UPDATED

You need to do + 0 after extracting the last two characters to convert it to a number

 SELECT inft.port FROM interfaces AS intf ORDER BY SUBSTRING(intf.port, LOCATE('/', intf.port)+1, LENGTH(intf.port)) + 0
0
source

More articles:


All Articles