SQL Order by Color (Hex Code)

Question

I have a table containing color options for a product. Color options include the hexadecimal color code that is used to create the user interface (HTML).

I would like to sort the lines so that the colors in the user interface look like a rainbow, and not the current order, which is sorted based on the color name (not very useful).

This is what my request looks like. I get decimal RGB values ​​from hexadecimal code. I just don’t know how to order it.

I studied color difference algorithms. They seem more useful for comparing the similarity of two colors, rather than sorting.

I am using MySQL.

select a.*, (a.c_r + a.c_g + a.c_b) color_sum
from (
    select co.customization_option_id, 
            co.designer_image_url,  
            concat(co.name, " (",cog.name, ")") name, 
            co.customization_option_group_id gr, 
            designer_hex_color,
            conv(substr(designer_hex_color, 1, 2), 16, 10) c_r,
            conv(substr(designer_hex_color, 3, 2), 16, 10) c_g,
            conv(substr(designer_hex_color, 5, 2), 16, 10) c_b
    from customization_options co 
            left join customization_option_groups cog 
            on cog.id = co.customization_option_group_id 
    where co.customization_id = 155 
            and co.customization_option_group_id 
            in (1,2,3,4)) a
order by ????

Answer 1

, . : RRGGBB.

, , Math.SO:

R '= R/255

G '= G/255

B '= B/255

Cmax = max (R ', G', B ')

Cmin = min (R ', G', B ')

Δ = Cmax - Cmin

, , Ruby, 200 RGB- , !

Ruby:

require 'paint'

def hex_to_rgb(hex)
  /(?<r>..)(?<g>..)(?<b>..)/ =~ hex
  [r,g,b].map {|cs| cs.to_i(16) }
end

def rgb_to_hue(r,g,b)
  # normalize r, g and b
  r_ = r / 255.0
  g_ = g / 255.0
  b_ = b / 255.0

  c_min = [r_,g_,b_].min
  c_max = [r_,g_,b_].max

  delta = (c_max - c_min).to_f

  # compute hue
  hue = 60 * ((g_ - b_)/delta % 6) if c_max == r_
  hue = 60 * ((b_ - r_)/delta + 2) if c_max == g_
  hue = 60 * ((r_ - g_)/delta + 4) if c_max == b_

  return hue
end

# sample uniformly at random from RGB space
colors = 200.times.map {  (0..255).to_a.sample(3).map { |i| i.to_s(16).rjust(2, '0')}.join   }

# sort by hue
colors.sort_by { |color| rgb_to_hue(*hex_to_rgb(color)) }.each do |color|
  puts Paint[color, color]
end

, , gem install paint .

:

SQL ORDER BY RGB_to_HUE (hex_color_code), SQL .

EDIT: dba.SE Ruby SQL.

Answer 2

by @dliff. , , , : " ". , .

( ) : , , 808080, R, G B . , /, , .

DELIMITER $$

DROP FUNCTION IF EXISTS `hex_to_hue`$$

CREATE FUNCTION `hex_to_hue`(HEX VARCHAR(6)) RETURNS FLOAT
BEGIN
    DECLARE r FLOAT;
    DECLARE b FLOAT;
    DECLARE g FLOAT;
    DECLARE MIN FLOAT;
    DECLARE MAX FLOAT;
    DECLARE delta FLOAT;
    DECLARE hue FLOAT;
    IF(HEX = '') THEN
        RETURN NULL;
    END IF;
    SET r = CONV(SUBSTR(HEX, 1, 2), 16, 10)/255.0;
    SET g = CONV(SUBSTR(HEX, 3, 2), 16, 10)/255.0;
    SET b = CONV(SUBSTR(HEX, 5, 2), 16, 10)/255.0;
    SET MAX = GREATEST(r,g,b);
    SET MIN = LEAST(r,g,b);
    SET delta = MAX - MIN;
    SET hue=
    (CASE 
        WHEN MAX=r THEN (60 * ((g - b)/delta % 6))
        WHEN MAX=g THEN (60 * ((b - r)/delta + 2))
        WHEN MAX=b THEN (60 * ((r - g)/delta + 4))
        ELSE NULL
    END);
    IF(ISNULL(hue)) THEN
        SET hue=999;
    END IF;
    RETURN hue;
END$$

DELIMITER ;

, .

Answer 3

, , , , .

, , , , , -

SELECT ...
FROM   (SELECT ... 
               ...
               ...
             , ci.color_order
        FROM   customization_options co 
               LEFT JOIN customization_option_groups cog 
                         ON cog.id = co.customization_option_group_id
               LEFT JOIN color_ ci
                         ON designer_hex_color = ci.color
        WHERE  ...) a
ORDER BY color_order

Another way is to convert the RGB color to a hue and use it as an order.
There is another formula for this conversion, depending on what order you want to have for the main color, they can all be found on the wikipedia page for hue , I can update the answer to help you convert one of them to T-SQL, if that necessary.

Answer 4

MySQL Hex to Hue Function. Based on Toby's answer. :)

CREATE FUNCTION `hex_to_hue`(hex varchar(6)) RETURNS float
BEGIN
declare r float;
declare b float;
declare g float;
declare min float;
declare max float;
declare delta float;
declare hue float;

set r = conv(substr(hex, 1, 2), 16, 10)/255.0;
set g = conv(substr(hex, 3, 2), 16, 10)/255.0;
set b = conv(substr(hex, 5, 2), 16, 10)/255.0;

set max = greatest(r,g,b);
set min = least(r,g,b);

set delta = max - min;

set hue=
(case 
    when max=r then (60 * ((g - b)/delta % 6))
    when max=g then (60 * ((b - r)/delta + 2))
    when max=b then (60 * ((r - g)/delta + 4))
    else null
end);

RETURN hue;
END