CODEWITHSUNDEEP
phpmysqlsqlsorting
Pradeep Singh
Pradeep Singh

Reputation: 3634

Sorting from smallest to largest

I have a field capacity in mytable .

I want to sort the fetch data by capacity from smallest to largest

these values are

6" x 12"
6" x 12"
6" x 18"
6" x 12"
10" x 20"
12" x 24"

I want this results

6" x 12"
6" x 12"
6" x 12"
6" x 18"
10" x 20"
12" x 24"

if I using this query

select * from mytable order by capacity ASC

than the result is

10" x 20"
12" x 24"
6" x 12"
6" x 12"
6" x 12"
6" x 18"

if I using this query

select * from mytable order by capacity + 0 ASC

than the result is

6" x 12"
6" x 12"
6" x 18"
6" x 12"
10" x 20"
12" x 24"

Upvotes: 1

Views: 863

Answers (6)

Yes Barry
Yes Barry

Reputation: 9876

One way you could do it is to loop through the results and multiply each value together. Using a custom sort afterwards you could then have the result you're looking for.

$sortedArray = array();
$array       = array(
    '6" x 12"', '6" x 18"', '6" x 12"', '6" x 12"', '10" x 12"'
);

// loop through rowset
foreach ($array as $row) {
    $parts = explode(' x ', str_replace('"', '', $row));
    $sortedArray[][($parts[0] * $parts[1])] = $row;
}

// custom sort - PHP 5.3 required for anonymous functions
usort($sortedArray, function($a, $b) {
    return (array_keys($a) > array_keys($b));
});

Alternatively, if you do not have PHP 5.3, then you could simply change the usort like this:

function mySort($a, $b)
{
    return (array_keys($a) > array_keys($b));
}

usort($sortedArray, 'mySort');

Upvotes: 1

jmilloy
jmilloy

Reputation: 8365

If you cannot refactor the database to store the size in two numeric columns, but you want to achieve the sort in mysql rather that php post-processing, you can try to extract the dimensions and cast the values to numbers, something like

SELECT dimensions
FROM mytable
ORDER BY
    CAST(SUBSTRING_INDEX(dimensions, '"', 1) AS SIGNED) * 
    CAST(SUBSTRING_INDEX(LEFT(dimensions, LENGTH(dimensions)-1), ' ', -1) AS SIGNED)
    ASC

Or, better, similar to Ashwini's answer:

SELECT dimensions
FROM mytable
ORDER BY
    (SUBSTRING_INDEX(dimensions, 'x', 1) + 0) * 
    (SUBSTRING_INDEX(dimensions, 'x', -1) + 0)
    ASC

You'll probably need to tweak the two substring index parts.

Another way to extract the values you need:

  • extract the first dimension (the substring from the beginning to the first ": LEFT(dimensions, INSTR(dimensions, '"'))

  • extract the second dimension (the substring from after the x to 1 less than the end): RIGHT(LEFT(dimensions, LENGTH(dimensions-1)), INSTR(dimensions, 'x') + 1)

Upvotes: 1

Ricardo Souza
Ricardo Souza

Reputation: 16466

Do a favor for your self if you can. Create 2 separete columns for this data and, if you don't want to concatenate the values in PHP, contactenate with SQL:

select contact(width, '" x ', height, '"') as capacity from mytable order by width asc, height asc

Also, ordering by width and height separatelly is much more eficient and produces the desired result.

Upvotes: 1

John Woo
John Woo

Reputation: 263843

This is a very bad schema for a table. The best way is to create a table that separates them (the values) into two columns: width and height. Example,

CREATE TABLE Dimension
(
   ID INT AUTO_INCREMENT,
   `Width` INT,
   `Height` INT,
   CONSTRAINT tb_pk PRIMARY KEY (ID)
);

then you can now insert your records like this,

INSERT INTO Dimension (`Width`, `Height`) VALUES (6, 12), (6,18), (10, 12);

and simply use this query to order the result

SELECT *
FROM Dimension
ORDER BY (`width` * `height`) DESC

But to answer your question without changing the schema, create a USER DEFINE FUNCTION in you server,

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 use this query,

SELECT CAST(SPLIT_STR(REPLACE(`capacity`, '"', '') ,'x', 1) as SIGNED) width,
       CAST(SPLIT_STR(REPLACE(`capacity`, '"', '') ,'x', 2) as SIGNED) height
FROM tableName
ORDER BY  (width * height) DESC

SQLFiddle Demo

Upvotes: 4

Ashwini Agarwal
Ashwini Agarwal

Reputation: 4858

You should try this...

SELECT *, SUBSTRING_INDEX(capacity, 'x', 1) AS width, 
    SUBSTRING_INDEX(capacity, 'x', -1) AS height 
FROM mytable 
ORDER BY width+0 ASC, height+0 ASC

Upvotes: 3

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

change this query 

select * from mytable order by capacity ASC

to

select * from mytable order by trim(capacity) ASC

It also helped me two days ago...

Upvotes: 1

Related Questions