[SOLVED] ORDER BY english measurement

[ober]

So I have a table with products that have diameters (I didn't design the table... just taking over a project).

 

The diameters are like this: 1/8, 1/4, 3/16, 1 1/4, etc

 

I'm guessing it's a pipe dream to be able to sort them by a simple ORDER BY clause smallest to largest, but I thought I'd ask.  I really hope I don't need to pull these into an array, do some math and sort with PHP.

[Daniel0]

I don't think that'll be possible. The easiest way would be to run through them all, make them a float value and update the rows. Hereinafter you can use ORDER BY. Alternately, if you need it expressed like a fraction, you could create a new column holding the float value for the purpose of the ordering.

[PFMaBiSmAd]

The following "might" work (assuming there is a space between any whole number and the fractional part) -

 

SELECT (REPLACE(your_size_col, " ", "+") + 0.0) as size FROM your_table ORDER BY size

 

The intent of the above - replace any space with a +. Any x y/z becomes x+y/z. When this is added to 0.0, this should evaluate the string as an expression (cross fingers at this point) and produce the proper decimal value. Then alias this as "size" that is then used in the ORDER BY.

 

It might be necessary to cast/convert the result of the REPLACE() function to get a number at that point.

[PFMaBiSmAd]

Forget my post above. The math operators + and / contained in the string are not evaluated. It would take splitting the strings into individual numbers and then doing the math.

[PFMaBiSmAd]

In case you want to do this in a query, the following works for various values I used for testing -

 

$query = "SELECT size, 0.0 + IF(SUBSTRING(size,1,LOCATE(' ', size) - 1),
SUBSTRING(size,1,LOCATE(' ', size) - 1) + SUBSTRING(size,1,LOCATE('/', size) - 1) / SUBSTRING(size,LOCATE('/', size) + 1,100),
IF(SUBSTRING(size,1,LOCATE('/', size) - 1),
SUBSTRING(size,1,LOCATE('/', size) - 1) / SUBSTRING(size,LOCATE('/', size) + 1,100), size)) as sz
FROM your_table ORDER BY sz";

 

Here is how it works -

 

If there is a space, produce x+y/z

else if there is a /, produce y/z

else x

[PFMaBiSmAd]

Third time's the charm (the above equation did not correctly find the start of the "y" in - x y/z

 

$query = "SELECT size,
0.0 + IF(SUBSTRING(size,1,LOCATE(' ', size) - 1),
SUBSTRING(size,1,LOCATE(' ', size) - 1) +
SUBSTRING(size,LOCATE(' ', size),LOCATE('/', size) - 1) /
SUBSTRING(size,LOCATE('/', size) + 1,100),
IF(SUBSTRING(size,1,LOCATE('/', size) - 1),
SUBSTRING(size,1,LOCATE('/', size) - 1) /
SUBSTRING(size,LOCATE('/', size) + 1,100), size)) as sz
FROM $table_name ORDER BY sz";

[rajivgonsalves]

try this a little complex  I too got confused making it

 

select size, CONVERT(SUBSTRING(size, 1,INSTR(size,' ')-1),DECIMAL)+CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(size, ' ', -1),'/',1),DECIMAL)/CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(size, ' ', -1),'/',-1),DECIMAL) as sz from $table_name order by sz;

 

 

hope its helpful

[ober]

Wow... you guys are awesome   I'll give those a shot and let you know.

[PFMaBiSmAd]

rajivgonsalves, your query did not handle whole numbers greater than 1. It did handle whole+fractional greater than 1. This worked - 10 13/64, but 2,3,10... did not.

 

10 1.0000

2 1.0000

3 1.0000

 

[ober]

PFMaBiSmAd, you rock.  I don't know how long it took you to come up with that, but I owe you!  That worked like a charm!

[rajivgonsalves]

did not think about that thanks for pointing it out  ... here you go second try 

 

select size, CONVERT(SUBSTRING(size, 1,INSTR(size,' ')-1),DECIMAL)+(CASE WHEN INSTR(size,'/') > 0 THEN CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(size, ' ', -1),'/',1),DECIMAL)/CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(size, ' ', -1),'/',-1),DECIMAL) ELSE size END) as sz from $table_name order by sz;

[banacan]

Hey,

 

I stumbled across this post and it just happens that I need to do something similar, although maybe easier. 

 

I have a table with sizes saved in decimal format in a FLOAT, but I need to display them in fractional form on-screen.  How can I accomplish that?  And more specifically, how can I show the lowest denominator possible for a given number.  I need precision down to 1/32" but I don't want 3.5" expressed as 3-16/32", I want it expressed as 3-1/2".

 

Hope someone can help.