pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan't get the order I want after inserting new rows
4+ messages / 2 participants
[nested] [flat]
* can't get the order I want after inserting new rows
@ 2004-12-17 09:46 Marcus Claesson <m.claesson@student.ucc.ie>
0 siblings, 1 reply; 4+ messages in thread
From: Marcus Claesson @ 2004-12-17 09:46 UTC (permalink / raw)
To: pgsql-sql
Hi!
I have a problem with getting the order I want on a table after new rows
have been inserted. I try to simplify it...:
I want to have a one-to-one relationship between 'name' and 'full'.
Every 'name' (or'full') have one or more 'parts'. The higher the 'score'
the lower the 'full', but for my program I have to keep every row with
the same 'name' next to each other, with ascending 'part' number. I
don't want to use 'name' as an identifier since they can be long
sentences.
In other words, I have this old table:
SELECT * from table ORDER BY full,part;
name full part score
---- --- --- -----
a 1 1 900
a 1 2 500
b 2 1 800
c 3 1 700
c 3 2 600
c 3 3 500
and I insert these rows into the same table:
(there's nothing I can do about these 'full' values)
d 1 1 1000
d 1 2 400
e 2 1 900
e 2 2 500
f 3 1 700
g 4 1 600
And after some manipulation (that I hope someone can help me with) I
want the query above (SELECT * from table ORDER BY full,part) to give
this:
d 1 1 1000
d 1 2 400
a 2 1 900
e 3 1 900
b 4 1 800
c 5 1 700
c 5 2 600
c 5 3 500
f 6 1 700
g 7 1 600
rather than
a 1 1 900
a 1 2 500
d 1 1 1000
d 1 2 400
b 2 1 800
e 2 1 900
e 2 2 500
c 3 1 700
c 3 2 600
c 3 3 500
f 3 1 700
g 4 1 600
Very grateful for any feedback!
Marcus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't get the order I want after inserting new rows
@ 2004-12-17 12:37 Richard Huxton <dev@archonet.com>
parent: Marcus Claesson <m.claesson@student.ucc.ie>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-12-17 12:37 UTC (permalink / raw)
To: Marcus Claesson <m.claesson@student.ucc.ie>; +Cc: pgsql-sql
Marcus Claesson wrote:
> Hi!
>
> I have a problem with getting the order I want on a table after new rows
> have been inserted. I try to simplify it...:
> In other words, I have this old table:
> SELECT * from table ORDER BY full,part;
>
> name full part score
> ---- --- --- -----
> a 1 1 900
> a 1 2 500
> and I insert these rows into the same table:
> (there's nothing I can do about these 'full' values)
>
> d 1 1 1000
> d 1 2 400
> And after some manipulation (that I hope someone can help me with) I
> want the query above (SELECT * from table ORDER BY full,part) to give
> this:
>
> d 1 1 1000
> d 1 2 400
> a 2 1 900
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).
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?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't get the order I want after inserting new rows
@ 2004-12-17 14:32 Marcus Claesson <m.claesson@student.ucc.ie>
0 siblings, 1 reply; 4+ messages in thread
From: Marcus Claesson @ 2004-12-17 14:32 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't get the order I want after inserting new rows
@ 2004-12-17 16:43 Richard Huxton <dev@archonet.com>
parent: Marcus Claesson <m.claesson@student.ucc.ie>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-12-17 16:43 UTC (permalink / raw)
To: Marcus Claesson <m.claesson@student.ucc.ie>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-12-17 16:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-17 09:46 can't get the order I want after inserting new rows Marcus Claesson <m.claesson@student.ucc.ie>
2004-12-17 12:37 ` Richard Huxton <dev@archonet.com>
2004-12-17 14:32 Re: can't get the order I want after inserting new rows Marcus Claesson <m.claesson@student.ucc.ie>
2004-12-17 16:43 ` Richard Huxton <dev@archonet.com>
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