Sorting HEX color values in rainbow order using Android java or sqlite

Question

Sorting HEX color values in rainbow order using Android java or sqlite

I have, among others, a textbox in my sqlite database that represents HEX color values like "92B01C". I am filling the listview with a cursor:

data = database.query("threads", newfields, where_clause, null, null, null, "CAST(" + newfields[0] + " AS TEXT)" + " " + "COLLATE NOCASE ASC");
dataAdapter.changeCursor(data);
dataAdapter.getCursor().requery();

Where newfields [0] is a six-digit sqlite database field.

My goal is to sort the list by HEX colors in "rainbow order" ie. all RED shades followed by GREEN shades, etc. etc.

I guess I'll have to convert the hex String values to RGB values and then somehow apply that to the database query.

So my questions are:

Can a db.query like this be added to sqlite or do I need to do it outside of the query somehow?
How do I split the hex value of a string into RGB colors so that they can be sorted in rainbow order? I was thinking about using Collections.sort, but then how would that be used in conjunction with database.query?
If this cannot be done in db.query, how do I apply any .sort collections to the list after sqlite database.query?

Any help would be great ...

UPDATE

Thanks for all the suggestions, this is where I am:

I have created a new integer db "color_sortorder" which I will use for the sort order of the colors.

I will loop the HEX db color string field and add it to the arraylist.

while (colorrawdata.isAfterLast() == false) {
    hexcoloritems.add(colorrawdata.getString(colorrawdata.getColumnIndex("colorref")));
    colorrawdata.moveToNext();
}

Then I sort the arraylist using this comparator:

class mycolorComparator implements Comparator<String> {
    public int compare(String strA, String strB) {

        float[] hsv1 = new float[3];
        float[] hsv2 = new float[3];

        int n1 = Integer.parseInt(strA, 16);
        int n2 = Integer.parseInt(strB, 16);

        Color.colorToHSV(n1, hsv1);
        Color.colorToHSV(n2, hsv2);

        if ((int)hsv1[0] == (int)hsv2[0]) {
            return (int)hsv1[2] - (int)hsv2[2];
        }

        return (int)hsv1[0] - (int)hsv2[0];

    }
}

Then I will loop the arraylist to populate the new db color sort field.

for (int x = 0; x < hexcoloritems.size(); x++) {
    database.execSQL("UPDATE threads SET color_sortorder='" + x + "' WHERE colorref='" + hexcoloritems.get(x) + "';");
}

The result is promising, but not entirely correct. The list of color types appears to be grouped, but within a grouping like green, the shades do not run from light to dark, but are instead sorted randomly (but have the correct shades of green).

This is the resulting order (first 20 of 520)

Setting db color_sortorder to 0 where db colorref=FAFAFA
Setting db color_sortorder to 1 where db colorref=FAFAFA
Setting db color_sortorder to 2 where db colorref=000000
Setting db color_sortorder to 3 where db colorref=de605e
Setting db color_sortorder to 4 where db colorref=F2ACAC
Setting db color_sortorder to 5 where db colorref=C16363
Setting db color_sortorder to 6 where db colorref=FAFAFA
Setting db color_sortorder to 7 where db colorref=B61F1E
Setting db color_sortorder to 8 where db colorref=951A18
Setting db color_sortorder to 9 where db colorref=CC2523
Setting db color_sortorder to 10 where db colorref=ffd5d5
Setting db color_sortorder to 11 where db colorref=FFCECE
Setting db color_sortorder to 12 where db colorref=FFE1E1
Setting db color_sortorder to 13 where db colorref=FFD5D5
Setting db color_sortorder to 14 where db colorref=D64542
Setting db color_sortorder to 15 where db colorref=8E3230
Setting db color_sortorder to 16 where db colorref=D6403D
Setting db color_sortorder to 17 where db colorref=DB3D38
Setting db color_sortorder to 18 where db colorref=FFC1BF
Setting db color_sortorder to 19 where db colorref=D58582