MySQL Group By Sorting?

Question

I have a MySQL DB that looks kind of like this...

id     Color    Length   Width   map_price   other_fields, etc...
------ -----    -----    -----   -----       -----
1      Red      2'3"     2'6"    125.0000    ...etc
2      Red      10'6"    12'     200.0000    ...etc
3      Red      12'      14'     300.0000    ...etc
4      Blue     3'       5'      135.0000    ...etc
5      Blue     5'8"     7'6"    160.0000    ...etc
6      Green    2'5"     3'      115.0000    ...etc
7      Yellow   9'2"     10'     200.0000    ...etc

I am attempting to construct a query to

GROUP BY Color
AND "use the highest values (determined by map_price)" to represent the group.

At the moment, when I GROUP BY Color - the LOWEST value in the fields seem to be defaulted to (or it could just be the lower ID #, although I can't reliably say id 2 > id 1 for my purposes). For example...

 Color   Length   Width   map_price   other_fields, etc...
 ------  ------   -----   ---------   -----
 Red     2'3"     2'6"    125.0000    ...etc
 Blue    3'       5'      135.0000    ...etc
 Green   2'5"     3'      115.0000    ...etc
 Yellow  9'2      10'     200.0000    ...etc

And what I'm after is actually...

 Color   Length   Width   map_price   other_fields, etc...
 ------  ------   -----   ---------   -----
 Red     12'      14'     300.0000    ...etc
 Blue    5'8"     7'6"    160.0000    ...etc
 Green   2'5"     3'      115.0000    ...etc
 Yellow  9'2      10'     200.0000    ...etc

The problem is this is the most concise way I can think of to articulate what I'm after, so unfortunately google, etc... has done me no good. I just need a finger in the right direction.

Do you want to compare characters in length or use them as digits? `select * from (select '120\'' union select '2\'') t order by 1 desc;` will result in 2' being large than 120'. For red, if length was highest in id 3 and width was highest in id 2 and price was highest in id 1, which record would you display? — zedfoxus, Dec 23 '15 at 16:38
This data above is just to illustrate my example...not based on anything, really. In my case, and in this subset, higher square footage will always result in a higher map_price. So, map_price will probably be the most useful to use. — SoWizardly, Dec 23 '15 at 16:47
Ah, good point. I understand now and have added an answer. Thank you. — zedfoxus, Dec 23 '15 at 16:53

ScaisEdge · Answer 1 · 2015-12-23T16:33:45.660

1

Seem you need a select max

select color, max(length), max(width), max(map_price) 
from your_table 
group by color;

or

select color, length, width, map_price, other_column
from your_table 
where (color, map_price)  in  (select color, max(map_price)  
                                 from     your_table group by color)

edited Dec 23 '15 at 16:33

answered Dec 23 '15 at 16:24

ScaisEdge

131,976
10
91
107

YES! Thank you. This is what I'm looking for! I just need to be able to select all values from the row with the max(map_price)....any ideas? (not all values are numeric, so max() won't work) – SoWizardly Dec 23 '15 at 16:26

score 1 · Accepted Answer · answered Dec 23 '15 at 16:52

You could write something like this:

select * 
from colors c 
where exists (
  select 1 
  from (select color, max(map_price) pr from colors group by color) x   
  where color = c.color and pr = c.map_price
);

+------+--------+--------+-------+-----------+
| id   | color  | length | width | map_price |
+------+--------+--------+-------+-----------+
|    3 | Red    | 12'    | 14'   |  300.0000 |
|    5 | Blue   | 5'8"   | 7'6"  |  160.0000 |
|    6 | Green  | 2'5"   | 3'    |  115.0000 |
|    7 | Yellow | 9'2"   | 10'   |  200.0000 |
+------+--------+--------+-------+-----------+

Thank you so much! It took me about an hour to rewrite my query (more complicated than this simple example) to test this method out - but it works BEAUTIFULLY! — SoWizardly, Dec 23 '15 at 17:59

score 0 · Answer 3 · answered Dec 23 '15 at 16:21

0

Based on your requirements here, I think you could solve it using

ORDER BY map_price DESC

that may just be a temporary fix, but like you say, without being able to articulate the question, it's difficult to answer.

answered Dec 23 '15 at 16:21

Ande Frankland

11
6

i actually tried that first, having never done that before. It WORKED, however, it ordered the results AFTER they had been grouped. not before...which is what I'm trying to accomplish. For the record, I never said I COULDN'T articulate what I need - I just couldn't do it *concisely*. – SoWizardly Dec 23 '15 at 16:22

score 0 · Answer 4 · edited May 23 '17 at 11:52

0

To the next guy googling this question and looking for an answer...This post was also incredibly useful. MySQL order by before group by

edited May 23 '17 at 11:52

Community

1
1

answered Dec 23 '15 at 16:45

SoWizardly

387
1
6
16

score 0 · Answer 5 · answered Dec 23 '15 at 17:05

0

Try this SELECT color, MAX(length), MAX(width), MAX(map_price) FROM your_table GROUP BY color;

answered Dec 23 '15 at 17:05

Vipin Jain

3,686
16
35

MySQL Group By Sorting?

5 Answers5