CODEWITHSUNDEEP
sql-server
Jprada
Jprada

Reputation: 97

Order by group of values

I have a list of locations in a table. The first position begin in '2' and finish in '8', the second position begin in letter 'A' and finish in 'P' . Finally every location has seven elements like this example:

SELECT Location FROM WAREHOUSE_LOCATIONS 

Location |
---------|
2A-1     |
2A-2     | 
2A-3     |
2A-4     |
2A-5     |
2A-6     |
2A-7     |
2B-1     |
2B-2     |
2B-3     |
2B-4     |
2B-5     |
2B-6     |
2B-7     |
2C-1     |
...
3A-1     |
...
4A-1     |
...
etc...

I want to order this locations like this:

Location |
---------|
2A-1     |
2B-1     | 
2C-1     |
2D-1     |
....
2P-1     |
2A-2     |
2B-2     | 
2C-2     |
...
2P-2     |
2A-3     |
2B-3     |
2C-3     |

How can i do that?

Upvotes: 0

Views: 54

Answers (1)

rory.ap
rory.ap

Reputation: 35260

You could do:

ORDER BY CAST(RIGHT(Location, LEN(Location) - CHARINDEX('-', Location)) AS INT),
LEFT(Location, CHARINDEX('-', Location) - 1)

Upvotes: 3

Related Questions