Subject: Re: can't get the order I want after inserting new rows
From: Marcus Claesson <m.claesson@student.ucc.ie>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: 
Message-Id: <1103293951.17814.568.camel@morpheus.ucc.ie>
Mime-Version: 1.0
Date: 17 Dec 2004 14:32:31 +0000
Content-Transfer-Encoding: 7bit

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.  

> Are you trying to summarise, and if so by what?
> Can you explain how you would do this by hand.
> Could you provide the actual table definition?

I'm trying to summarize parsed outputs from the bioinformatics
application 'blast', where 'name' is hit-name, 'full' is hit, and 'part'
is sub-hits called HSPs (within the same hit). Score is score.

If I was doing it by hand I would sort the rows descending according to
score, and give them new 'full' values, starting from 1. But if a 'name'
has more than one 'part' I would give them the same new 'full' value as
its first 'part', even if they would have lower scores.

Actually, I could add the new data in two different ways. Either as
before:

d       1       1       1000
d       1       2       400
e       2       1       900
e       2       2       500
f       3       1       700
g       4       1       600

or (by using the old data's highest 'full' as offset)

d       3       1       1000
d       3       2       400
e       5       1       900
e       5       2       500
f       6       1       700
g       7       1       600


The table defintion would be
CREATE TABLE table(name TEXT NOT NULL,full INTEGER,part INTEGER)

Hope I haven't confused you more...;)

Marcus