agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: Sort a table by a column value that is a column name?
5+ messages / 4 participants
[nested] [flat]
* Sort a table by a column value that is a column name?
@ 2021-07-09 16:59 overland <overland@recarea.com>
2021-07-09 16:32 ` Re: Sort a table by a column value that is a column name? Rob Sargent <robjsargent@gmail.com>
2021-07-09 16:42 ` Re: Sort a table by a column value that is a column name? Steve Midgley <science@misuse.org>
2021-07-09 17:01 ` Re: Sort a table by a column value that is a column name? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: overland @ 2021-07-09 16:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort a table by a column value that is a column name?
2021-07-09 16:59 Sort a table by a column value that is a column name? overland <overland@recarea.com>
@ 2021-07-09 16:32 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2021-07-09 16:32 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 7/9/21 10:59 AM, overland 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;
>
>
>
And I take it your having trouble with "name".
Keep in mind that one can sort by values not in the select criteria but
that won't help the client much.
I think you'll have to do a case analysis on the available values of
descriptor.text (which I suspect the client is already doing) and
formulate the request based on the clients choice of, in this case,
either "attribute" or "property". You can either prepare a map of all
possible statements or generate the sql on-demand (or maybe generate the
prepared statements as needed and populate the map)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort a table by a column value that is a column name?
2021-07-09 16:59 Sort a table by a column value that is a column name? overland <overland@recarea.com>
@ 2021-07-09 16:42 ` Steve Midgley <science@misuse.org>
2021-07-09 19:08 ` Re: Sort a table by a column value that is a column name? overland <overland@recarea.com>
2 siblings, 1 reply; 5+ messages in thread
From: Steve Midgley @ 2021-07-09 16:42 UTC (permalink / raw)
To: overland <overland@recarea.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Fri, Jul 9, 2021 at 9:03 AM overland <overland@recarea.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort a table by a column value that is a column name?
2021-07-09 16:59 Sort a table by a column value that is a column name? overland <overland@recarea.com>
2021-07-09 16:42 ` Re: Sort a table by a column value that is a column name? Steve Midgley <science@misuse.org>
@ 2021-07-09 19:08 ` overland <overland@recarea.com>
0 siblings, 0 replies; 5+ messages in thread
From: overland @ 2021-07-09 19:08 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Fri, 2021-07-09 at 09:42 -0700, Steve Midgley wrote:
> On Fri, Jul 9, 2021 at 9:03 AM overland <overland@recarea.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
Ha-ha.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort a table by a column value that is a column name?
2021-07-09 16:59 Sort a table by a column value that is a column name? overland <overland@recarea.com>
@ 2021-07-09 17:01 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-07-09 17:01 UTC (permalink / raw)
To: overland <overland@recarea.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Fri, Jul 9, 2021 at 9:02 AM overland <overland@recarea.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-07-09 19:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-09 16:59 Sort a table by a column value that is a column name? overland <overland@recarea.com>
2021-07-09 16:32 ` Rob Sargent <robjsargent@gmail.com>
2021-07-09 16:42 ` Steve Midgley <science@misuse.org>
2021-07-09 19:08 ` overland <overland@recarea.com>
2021-07-09 17:01 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox