Re: Sort a table by a column value that is a column name?

On Fri, Jul 9, 2021 at 9:02 AM overland <overland(at)recarea(dot)com> wrote:

> An example is below that is quick and dirty and shows what I'm trying to
> do. There isn't an error when the query is executed, yet the sort doesn't
> work and fails sighlently. Is there another way to
> accomplish the same thing?
>
> SELECT attribute, property, descid
> FROM list AS l
> JOIN descriptor AS d ON l.descid = d.id
> WHERE l.id < 4
> ORDER BY name;
>

Change that to:

... ORDER BY CASE WHEN d.name = 'attribute' THEN attribute ELSE property
END;

And it should give you the desired results.

The structure of an SQL command cannot change in response to data so you
need to account for all possible values in the "name" column and choose the
desired column to pull the data value from when you write the query.

The whole point of "ORDER BY <column_name>" is that the values in
<column_name> are compared to each other and the final sort order of the
output corresponds to that comparison order. When you ran the query it
sorted the four "attribute" valued rows which, as they are all equal,
basically means no sorting. It worked just fine, it was just your
expectations or understanding of how sorting works that was incorrect.

(This answer, in less detail, it what I posted to Reddit though it got
buried deep in a reply chain)

David J.