X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3A94B3CC250 for ; Fri, 17 Dec 2004 09:47:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68060-05 for ; Fri, 17 Dec 2004 09:46:57 +0000 (GMT) Received: from mail3.ucc.ie (mail3.ucc.ie [143.239.128.103]) by svr1.postgresql.org (Postfix) with ESMTP id 9FEA13CC203 for ; Fri, 17 Dec 2004 09:46:57 +0000 (GMT) Received: from morpheus.ucc.ie (morpheus.ucc.ie [143.239.183.39]) by mail3.ucc.ie (8.12.10/8.12.10) with ESMTP id iBH9ko6j024282 for ; Fri, 17 Dec 2004 09:46:50 GMT Subject: can't get the order I want after inserting new rows From: Marcus Claesson To: pgsql-sql@postgresql.org Content-Type: text/plain Organization: Message-Id: <1103276805.17814.493.camel@morpheus.ucc.ie> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 17 Dec 2004 09:46:45 +0000 Content-Transfer-Encoding: 7bit X-MDdb: 110/010/2245/Sw/Fs/SA/0/1.84:040206.182155 X-SA-Skip: yes X-Scanned-By: MIMEDefang 2.36 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/114 X-Sequence-Number: 19734 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