can't get the order I want after inserting new rows - Mailing list pgsql-sql

From Marcus Claesson
Subject can't get the order I want after inserting new rows
Date
Msg-id 1103276805.17814.493.camel@morpheus.ucc.ie
Whole thread Raw
Responses Re: can't get the order I want after inserting new rows
List 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



pgsql-sql by date:

Previous
From: "Richard Sydney-Smith"
Date:
Subject: Table History
Next
From: Kenneth Gonsalves
Date:
Subject: sql can i substitute