> 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
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...;)
pgsql-sql by date
|Next:||From: Bruno Wolff III||Date: 2004-12-17 14:56:53|
|Subject: Re: sql can i substitute|
|Previous:||From: Achilleus Mantzios||Date: 2004-12-17 14:08:53|
|Subject: Re: [despammed] sql can i substitute|