From: Marcus Claesson <m.claesson@student.ucc.ie>
To: pgsql-sql@postgresql.org
Subject: can't get the order I want after inserting new rows
Date: 17 Dec 2004 09:46:45 +0000
Message-ID: <1103276805.17814.493.camel@morpheus.ucc.ie> (raw)
Hi!
I have a problem with getting the order I want on a table after new rows
have been inserted. I try to simplify it...:
I want to have a one-to-one relationship between 'name' and 'full'.
Every 'name' (or'full') have one or more 'parts'. The higher the 'score'
the lower the 'full', but for my program I have to keep every row with
the same 'name' next to each other, with ascending 'part' number. I
don't want to use 'name' as an identifier since they can be long
sentences.
In other words, I have this old table:
SELECT * from table ORDER BY full,part;
name full part score
---- --- --- -----
a 1 1 900
a 1 2 500
b 2 1 800
c 3 1 700
c 3 2 600
c 3 3 500
and I insert these rows into the same table:
(there's nothing I can do about these 'full' values)
d 1 1 1000
d 1 2 400
e 2 1 900
e 2 2 500
f 3 1 700
g 4 1 600
And after some manipulation (that I hope someone can help me with) I
want the query above (SELECT * from table ORDER BY full,part) to give
this:
d 1 1 1000
d 1 2 400
a 2 1 900
e 3 1 900
b 4 1 800
c 5 1 700
c 5 2 600
c 5 3 500
f 6 1 700
g 7 1 600
rather than
a 1 1 900
a 1 2 500
d 1 1 1000
d 1 2 400
b 2 1 800
e 2 1 900
e 2 2 500
c 3 1 700
c 3 2 600
c 3 3 500
f 3 1 700
g 4 1 600
Very grateful for any feedback!
Marcus
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: m.claesson@student.ucc.ie
Subject: Re: can't get the order I want after inserting new rows
In-Reply-To: <1103276805.17814.493.camel@morpheus.ucc.ie>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox