From: Richard Huxton <dev@archonet.com>
To: Marcus Claesson <m.claesson@student.ucc.ie>
Cc: pgsql-sql@postgresql.org
Subject: Re: can't get the order I want after inserting new rows
Date: Fri, 17 Dec 2004 16:43:14 +0000
Message-ID: <41C30CA2.4050203@archonet.com> (raw)
In-Reply-To: <1103293951.17814.568.camel@morpheus.ucc.ie>
References: <1103293951.17814.568.camel@morpheus.ucc.ie>
Marcus Claesson wrote:
> Hi Richard,
>
>
>>Just looking at the start of your output, you are missing some rows
>>(a/1/1) and have replaced others (a/2/1 isn't in your data).
>
>
> Yes, that's exactly it. There's no mistake. The only column I need to
> update is 'full', by giving every row a new 'full', based on its
> 'score'. The tricky thing is that if a 'name' has several 'parts', then
> all those 'parts' should have the same new 'full' value. Not sure if I'm
> making much sense here...Just ask away if it's still unclear.
OK - so if I re-arrange the columns: (name*, part*, score, full)
The primary key is (name,part), (score) is what you measure and (full)
is just for ordering.
So - you want to update "full" so it reflects the sortorder something like:
SELECT name, max(score) FROM table
GROUP BY name ORDER BY max(score) DESC;
You can then get the order you want with something like:
SELECT t1.name, t1.part, t1.score, t2.order_score
FROM table t1,
(
SELECT name, max(score) AS order_score
FROM table GROUP BY name
) AS t2
WHERE
t1.name = t2.name
ORDER BY
t2.order_score DESC, t1.part ASC;
Write a small plpgsql function to process the table in that order and
update "full" accordingly. Actually, I'd add a SERIAL primary key and
have a separate table for "full" - that way you can just delete the
sorting values and replace them in one go.
HTH
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, m.claesson@student.ucc.ie
Subject: Re: can't get the order I want after inserting new rows
In-Reply-To: <41C30CA2.4050203@archonet.com>
* 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