Order By Hell.....

[Roq]

I'm a network engineer and at my company we have many sites in random cities around the US, EU, etc... Our hostnames look something like this...

 

cr1.se1

cr2.se1

cr1.se2

cr2.se2

cr1.se3

cr2.se3

cr1.bo1

cr2.bo1

cr1.bo2

cr2.bo2

 

This is just an example but it's basically "Core Router X, Seattle1, Seattle 2, Boton 1" etc....The problem is I want to sort these based on site, and not device type.  Obviously sorting by hostname gives all the ASA's, then all the border routers, then core routers, etc etc....I care more about grouping by everything AFTER the period.....

 

Any ideas?

 

 

 

 

 

 

[PFMaBiSmAd]

Without an example of what result you expect for the example data you posted, it is not possible to help you.

[kickstart]

Hi

 

Think he wants it in the order

 

cr1.bo1

cr2.bo1

cr1.bo2

cr2.bo2

cr1.se1

cr2.se1

cr1.se2

cr2.se2

cr1.se3

cr2.se3

 

However without using substring functions to extract it (which would be slow) not sure I can see a way of doing it.

 

Would be better if the original table design split that code into 2 seperate string fields.

 

All the best

 

Keith

[Roq]

Keith, that is exactly what I want.  I've tried some substring functions but can't seem to get them to work (with MySQL at least, I can do it on the PHP side of things)... Could you give an example of how to get them to sort like that? 

 

Just finish a simple query like this:

select * from devicelist order by ***substring functions of the hostname field***

[kickstart]

Hi

 

I would do something like this:-

 

SELECT RouterId, SUBSTR( RouterId, 1, 3 ) AS RouterType, SUBSTR( RouterId, 5, 3 ) AS RouterLocation
FROM RoutersTable
ORDER BY RouterLocation, RouterType

 

All the best

 

Keith

[Roq]

That is very close.  The problem is that we're being strict on the 5, 3 part, and I have 4 devices that don't match the exact standard, since they are:

csr1.bo1

csr2.bo1

csr1.se1

csr2.se1

 

These devices unfortunately make the 'RouterLocation' .bo and .se, isntead of bo1 and se1.  I've tried dabbling with REVERSE() but couldn't seem to get it right.  The problem with just doing a reverse is it starts sorting by the 1's, then 2's, etc...

 

Since the first character of the location is either 5 or 6, I don't know the beginning of the Location if I start from the beginning of the string.  However, if we could count backwards from the end 3 places I think that would work perfectly.  Thoughts?

[PFMaBiSmAd]

SUBSTR() accepts negative values to refer to the end of the string. See the mysql documentation for examples.

[Roq]

Thanks I tried that before but couldn't figure it out...However with the above query, and changing the values to be negative, this query works fine:

 

SELECT id, device, SUBSTR( device, 1, 3 ) AS routertype, SUBSTR( device, -3, 3 ) AS routerlocation
FROM devicelist_corp_routers
ORDER BY routerlocation, routertype

 

Thanks a lot for your help everyone, this forum always seems to point me in the right direction.

[roopurt18]

DROP TABLE IF EXISTS foojunk;# MySQL returned an empty result set (i.e. zero rows).
CREATE TABLE foojunk(
tcol VARCHAR( 12 )
);# MySQL returned an empty result set (i.e. zero rows).
INSERT INTO foojunk( tcol ) 
VALUES (
'cr1.se1'
), (
'cr2.se1'
), (
'cr1.se2'
), (
'cr2.se2'
), (
'cr1.se3'
), (
'cr2.se3'
), (
'cr1.bo1'
), (
'cr2.bo1'
), (
'cr1.bo2'
), (
'cr2.bo2'
);# Affected rows: 10

SELECT * 
FROM (
    SELECT SUBSTR( tcol, 1, dot_pos -1 ) AS front_part, SUBSTR( tcol, dot_pos +1 ) AS back_part
    FROM (
        SELECT tcol, LOCATE(  '.', tcol ) AS  `dot_pos` 
        FROM foojunk
        ) AS t
    ) AS s
ORDER BY back_part
;

[Roq]

Oh snap, does that query look for the dot (.) in the string and then we have a clear routertype and routerlocation regardless of how many characters there are for the location and type?  Also assuming there is only one period (which there is in all of my 300+ routers/switches).

[roopurt18]

Oh snap, does that query look for the dot (.) in the string and then we have a clear routertype and routerlocation regardless of how many characters there are for the location and type?  Also assuming there is only one period (which there is in all of my 300+ routers/switches).

Yes and yes.

 

If you really wanted to do away with this ordering nonsense, you could add the two separate columns to the table and insert and update triggers that explode the string and populates the two new columns for you.  Then when you want to order you can just order by those columns.  The disadvantage is you'll add extra bytes to each table row.

 

Or you could write a database function and order that way.

 

I haven't used MySQL in ages so I'm assuming it supports triggers and user-defined functions.