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

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

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


Responses

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-12-17 14:56:53
Subject: Re: sql can i substitute
Previous:From: Achilleus MantziosDate: 2004-12-17 14:08:53
Subject: Re: [despammed] sql can i substitute

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