Menu
This is the result of my query, but it doesn't order 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 query:
SELECT inft.port FROM interfaces AS intf ORDER BY RIGHT(intf.port + 0, 2)
Second: sqlfiddle
474
SELECT *FROM yourTableName ORDER BY RIGHT(yourColumnName,3) yourSortingOrder; Just replace the 'yourSortingOrder' to ASC or DESC to set the ascending or descending order respectively. Here is the query to order by last 3 chars. Case 1 − Get the result in ascending order.
To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.
C) Sort rows by column's positions example SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1; In this example, the position of name column is 1 and credit_limit column is 2. In the ORDER BY clause, we used these column positions to instruct the Oracle to sort the rows.
SQL Server LEFT() Function The LEFT() function extracts a number of characters from a string (starting from left).
Try this:
SELECT port
FROM interfaces
ORDER BY SUBSTRING_INDEX(port, '/', 1), CAST(SUBSTRING_INDEX(port, '/', -1) AS SIGNED)
Check the 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 |
Why you need + 0 ?? Simply remove 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
The order is correct - lexicographically.
You need to convert them to a number.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
