Skip site navigation (1) Skip section navigation (2)

Re: can't get the order I want after inserting new rows

From: Richard Huxton <dev(at)archonet(dot)com>
To: Marcus Claesson <m(dot)claesson(at)student(dot)ucc(dot)ie>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: can't get the order I want after inserting new rows
Date: 2004-12-17 16:43:14
Message-ID: 41C30CA2.4050203@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Michael FuhrDate: 2004-12-18 09:26:33
Subject: Re: get branches & childs from database
Previous:From: RobertD.StewartDate: 2004-12-17 15:51:24
Subject: Re: Table History

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group