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

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

> I'm writing a program and I'm aiming to seperate logic from the database
> and at the same time optimize program performance. I was doing a sort on
> Postgresql 13 query results in a program but pushing
> the sort to postgresql would optimize performance. So I modified an
> existing query to do the sorting now and it isn't sorting as I want, but I
> don't know what to expect. I'm looking to sort a table
> using a column name that is stored in another table. I don't know the
> column to sort on when the query is written.
>
> 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?
>
>
>
>
>
> CREATE TABLE list (
> id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> attribute TEXT,
> property TEXT,
> descid INT
> );
>
>
> CREATE TABLE descriptor (
> id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> name TEXT
> );
>
> INSERT INTO descriptor(name) VALUES('attribute'), ('property');
> INSERT INTO list(attribute, property, descid) VALUES('todo', 'camping', 1);
> INSERT INTO list(attribute, property, descid) VALUES('hooplah',
> 'glamping', 1);
> INSERT INTO list(attribute, property, descid) VALUES('stuff', 'other', 1);
> INSERT INTO list(attribute, property, descid) VALUES('car', 'bike', 2);
> INSERT INTO list(attribute, property, descid) VALUES('cat', 'hat', 2);
> INSERT INTO list(attribute, property, descid) VALUES('bat', 'that', 2);
>
>
>
>
> SELECT attribute, property, descid
> FROM list AS l
> JOIN descriptor AS d ON l.descid = d.id
> WHERE l.id < 4
> ORDER BY name;
>
> What do you mean by the "sort fails silently?" Do you mean the query runs
and returns data, but the data are not sorted by your name field?

I modified your sample slightly (to make it work with Pg 9.x in SQLFiddle):
http://sqlfiddle.com/#!17/feaff/1

I also changed l.id 3 to link to descid 2 (otherwise there's nothing to
sort - the records all have the same name).

It works fine for me.. What am I missing?

Steve