agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: overland <overland@recarea.com>
To: Steve Midgley <science@misuse.org>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Sort a table by a column value that is a column name?
Date: Fri, 09 Jul 2021 12:08:35 -0700
Message-ID: <111951a2dfa543294b70ddfcb44b92e06fc1e2c2.camel@recarea.com> (raw)
In-Reply-To: <CAJexoSJ15B5xBjTtFXmrHf1eYkufSARSz4Vq++6sCYuxjU-AYA@mail.gmail.com>
References: <1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel@recarea.com>
	<CAJexoSJ15B5xBjTtFXmrHf1eYkufSARSz4Vq++6sCYuxjU-AYA@mail.gmail.com>

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.

view thread (5+ messages)

Message-ID: <111951a2dfa543294b70ddfcb44b92e06fc1e2c2.camel@recarea.com>
Permalink:  ../111951a2dfa543294b70ddfcb44b92e06fc1e2c2.camel@recarea.com/
Also on:    postgresql.org/message-id/111951a2dfa543294b70ddfcb44b92e06fc1e2c2.camel@recarea.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: overland@recarea.com, science@misuse.org, pgsql-sql@lists.postgresql.org
  Subject: Re: Sort a table by a column value that is a column name?
  In-Reply-To: <111951a2dfa543294b70ddfcb44b92e06fc1e2c2.camel@recarea.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox