pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

ordering problems ...
3+ messages / 2 participants
[nested] [flat]

* ordering problems ...
@ 2003-04-15 10:33  David Goodwin <dof@codepoets.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: David Goodwin @ 2003-04-15 10:33 UTC (permalink / raw)
  To: pgsql-sql

Hello everyone,

I have two tables :

question_response and question_response_answer. 

There is a one to many relationship between the question_response and a
question_response_answer (schema below at the bottom).

When I execute :


select answer from question_response_answer qra, question_response qr 
WHERE qr.response_id = qra.response_id AND qr.personal_id =
'XXYYTEST2' AND qr.module_code = 'TEST990' AND qr.assessment_id = 109
AND qr.question_id = 125 ;

I get :

 answer
---------
 Foo Bar
 Fred
 No
 Yes


When I execute :
                                                                         
select * from question_response_answer;

I get :

response_id |   answer
-------------+------------
         148 | Yes
         148 | No
         148 | Fred
         148 | Foo Bar

I'd like to know why the ordering has changed, and is there anything I can do make it the same as it is on the "select * from question_response_answer" "version" ?

Running Postgres 7.3.2 on Linux.

Schema :
    Column     |         Type         |                       Modifiers
---------------+----------------------+--------------------------------------------------------
 response_id   | integer              | not null default
nextval('question_reponse_seq'::text)
 question_id   | integer              |
 personal_id   | character varying(9) |
 assessment_id | integer              |
 module_code   | character varying(9) |
Indexes: question_response_pkey primary key btree (response_id),
         question_response_question_id_key unique btree (question_id,
personal_id, assessment_id)

(there are foreign keys, but I've left them off)

tweektest=> \d question_response_answer;
Table "public.question_response_answer"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 response_id | integer | not null
 answer      | text    | not null
Indexes: question_response_answer_pkey primary key btree (response_id,
answer)
Foreign Key constraints: $1 FOREIGN KEY (response_id) REFERENCES
question_response(response_id) ON UPDATE NO ACTION ON DELETE CASCADE




Thanks in advance,
David.

-- 
David Goodwin 

[ dof at codepoets dot co dot uk   ]
[ http://www.codepoets.co.uk       ]




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ordering problems ...
@ 2003-04-15 10:47  David Goodwin <dof@codepoets.co.uk>
  parent: David Goodwin <dof@codepoets.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: David Goodwin @ 2003-04-15 10:47 UTC (permalink / raw)
  To: DIB Walid <walid.dib@inrialpes.fr>; +Cc: pgsql-sql

DIB Walid wrote :
> Hello,
> I think that Postgres use the default ORDER BY function:
> so in your first request there is a default order by on the "answer" 
> In order to obtain the same order in your second request, put"ORDER BY 
> ANSWER" in your request, because if you miss this point, postgres will order 
> by id automatically, i hope i understand your problem because my english is a 
> little weak.
> @+
> Walid
> INRIA Rhones Alpes

Hi,

Thanks for the speedy reply :)

I just discovered that if i put in a "order by qra.oid" it works ok.
If I run the query with "select * from ..." rather than "select answer
from .." the results are the same (ordered alphabetically by answer).

thanks
David.
-- 
David Goodwin 

[ dof at codepoets dot co dot uk   ]
[ http://www.codepoets.co.uk       ]




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ordering problems ...
@ 2003-04-15 13:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David Goodwin <dof@codepoets.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2003-04-15 13:49 UTC (permalink / raw)
  To: David Goodwin <dof@codepoets.co.uk>; +Cc: DIB Walid <walid.dib@inrialpes.fr>; pgsql-sql

David Goodwin <dof@codepoets.co.uk> writes:
> I just discovered that if i put in a "order by qra.oid" it works ok.

That isn't going to work for long --- for example, as soon as you update
any row in question_response_answer, physical order will stop matching
OID order.

The real answer to your question is that SQL does not consider the
ordering of a table to be significant.  If you want the rows of a query
result to be displayed in a particular order, you have to use ORDER BY.
Without that, the system is entitled to deliver the rows in any order
it finds convenient.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-04-15 13:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-15 10:33 ordering problems ... David Goodwin <dof@codepoets.co.uk>
2003-04-15 10:47 ` David Goodwin <dof@codepoets.co.uk>
2003-04-15 13:49   ` Tom Lane <tgl@sss.pgh.pa.us>

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