From: Marcus Claesson <m.claesson@student.ucc.ie>
To: pgsql-sql@postgresql.org
Subject: Re: can't get the order I want after inserting new rows
Date: 17 Dec 2004 14:32:31 +0000
Message-ID: <1103293951.17814.568.camel@morpheus.ucc.ie> (raw)
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
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: <1103293951.17814.568.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