Sorting/ordering in MySQL

Question

I'm having a little problem with trying to sort the contents of a table programs by the column prog_id which holds the id of each program in the following format:

prog_id

1.0.1, 1.0.2, 1.0.3, ..., 1.0.10, 1.0.11, ..., 1.1.0, 1.1.1 etc

When I sort by prog_id i get

1.0.1, 1.0.10, 1.0.11, 1.0.2, 1.0.3 ...

which is correct as far as MySQL goes but not correct for the order in which the data should display. I tried using another column, orderby in which I could save an index and order by that but I would have to enter the values manually and there are a few thousand rows in my table which would take quite a long time to do.

Any tricks I could use to get my data to display in the "proper" order? BTW, I'm using PHP & MySQL.

anothershrubery · Accepted Answer · 2011-03-29 10:58:17Z

You could split them into their constituent parts like:

SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 1),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 1 -1)) + 1),
       '.', '') AS id1,
       REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 2),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 2 -1)) + 1),
       '.', '') AS id2,
       REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 3),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 3 -1)) + 1),
       '.', '') AS id3
FROM programs
ORDER BY CAST(id1 AS INT(4)), CAST(id2 AS INT(4)), CAST(id3 AS INT(4))

The best method would be to create the the extra fields like yoda2k says, but if you don't have that access then you could use the above.

You could encapsulate that into a function like:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Then do:

SELECT SPLIT_STR(prog_id, '.', 1) AS id1,
   SPLIT_STR(prog_id, '.', 2) AS id2,
   SPLIT_STR(prog_id, '.', 3) AS id3,
FROM programs
ORDER BY CAST(id1 AS INT(4)), CAST(id2 AS INT(4)), CAST(id3 AS INT(4))

Devart · Accepted Answer · 2011-03-29 10:58:29Z

0

Not optimal solution -

...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)

Odd solution, but try it -

...ORDER BY INET_ATON(prog_id)

answered Mar 29, 2011 at 10:58

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

bikey77 Over a year ago

WOW! That is one hell of an inspiration!

yoda2k · Accepted Answer · 2011-03-29 10:46:08Z

0

You could use 3 fields e.g. major_version, minor_version, build_number, make them integer fields and use mysqls buildin "ORDER BY major_version,minor_version,build_number" which will order the fields in the desired way.

answered Mar 29, 2011 at 10:46

yoda2k

557 bronze badges

Collectives™ on Stack Overflow

Sorting/ordering in MySQL

prog_id

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

prog_id

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related