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

increment Rows in an SQL Result Set postgresql

From: Hassan Adekoya <hechy_man(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: increment Rows in an SQL Result Set postgresql
Date: 2006-07-15 17:46:40
Message-ID: loom.20060715T194555-176@post.gmane.org (view raw or flat)
Thread:
Lists: pgsql-performance
HI,

1. I have a function that given two parameter produces an arbitrary id, and
text. However arbitrary the id and text are, they are in certain order. i.e. it
is imperative that whatever processing I do, the order is preserved.

2. An odd thing happens when I perform a join on the result set such that the
order that I hope to preserved in destroyed. The same result set but different
ordering. I gather this is due to the query planner. Enough said.

I was hoping to insert a counter in the select query of 1. such that when I
perform the join of 2, I can order by the counter. 


i.e. 

1. select id, astext from function1(1,2) 
2. select id, astext, table2.name from function1(1,2) as tmp, table2 where
tmp.id = table2.id
  
when I perform 1., I get something of sort

id | astext
2  | abc
6  | efg
3  | fhg

I will like to preserve ordering....

When I perform 2, I get somthing of sort

id  | astext | table2.name
6   | efg    | joe
2   | abc    | zyi
3   | fgh    | mec

Can someone help such that I get something like 

id  | astext | table2.name | increment
6   | efg    | joe         | 2
2   | abc    | zyi         | 1
3   | fgh    | mec         | 3

Thanks!


Responses

pgsql-performance by date

Next:From: HassanDate: 2006-07-15 18:53:07
Subject: Re: increment Rows in an SQL Result Set postgresql
Previous:From: Craig A. JamesDate: 2006-07-15 17:27:20
Subject: Re: increment Rows in an SQL Result Set postgresql

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