SQL layer wont ORDER BY in CARTO VL

Question

I have a dataset that shows the borehole yield for fractured aquifers in South Africa. Using the "yield_ls" column, my CARTO VL legend looks like this (notice how the 4th item should be the first).

const aquiferViz = new carto.Viz(`
  color: ramp($yield_ls, teal)
  strokeColor: rgba(0,0,0,0.3)
  strokeWidth: 1
`);

I noticed that each legend item is assigned an ID column in the database ("sayld_id") starting from 6 to 10. Therefore if this database is ordered by the sayld_id ASC, then the yield_ls features go into a logical order. I tested this using the following SQL command in my CARTO dataset:

SELECT *
FROM fractured 
ORDER BY sayld_id ASC   

I then tried to do this when adding my the layer to a CARTO VL map using:

const fracturedSource = new carto.source.SQL(`
  SELECT *
    FROM fractured
    ORDER BY sayld_id ASC
  `);

But the legend remains the same as before. Is there a better way to correctly arrange the legend, or possibly move an item up or down in the legend order?

Answer 1

I think the problem here is that you're passing directly the attribute ($yield_ls) to the ramp expression, and the results are sorted by number of occurrences of each category.

In order to classify the content you're receiving, you've to use the buckets as follows:

color: ramp(buckets($yield_ls, [
    "Fractured 0.0 - 0.1 l/s",
    "Fractured 0.1 - 0.5 l/s"
    "Fractured 0.5 - 2.0 l/s"
    "Fractured 2.0 - 5.0 l/s",
    "Fractured > 5.0 l/s"
]), teal)

In this case, $yield_ls is a categorical attribute, you have to make sure that all the values passed to the buckets expression ("Fractured ...") match perfectly with all the category names you want to classify.