Re: Problem with result ordering

The question I'd ask before offering a solution is, "Does the order of the
id data matter, or is it a question of having all the results for a given id
together before proceeding to the next id?" The answer to this will
determine whether or not adding either a group by clause or an order by
clause will help.

Is there a reason you client app doesn't submit a simple select for each of
the required ids? You'd have to do some checking to see whether it pays to
have the ordering or grouping operation handled on the server or client.
Other options to consider, perhaps affecting performance and security, would
be parameterized queries or stored procedures.

Much depends on the design and implementation of your client app. I know,
e.g., that in ASP.NET 2, and later, you can handle multiple resultsets from
a single datasource, so a trivially simple SQL script that consists of the
simplest SELECT statements might be a viable option. But it is hard to
advise since you don't say if you have access to or control over the source
code for the client app or what it is written in.

In my experience, I always have to run some benchmarks for a given
distributed application to figure out how best to distribute the workload,
and there are always plenty of different ways to do things, with often big
differences in performance and security. It seems never to be trivial to
figure this out without some testing before a final decision. I can never
just assume that it is best to do all the processing in the RDBMS backend to
my apps.

HTH

Ted

----- Original Message -----
From: "Thorsten Körner" <t(dot)koerner(at)cappuccinosoft(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, January 25, 2007 10:45 AM
Subject: [GENERAL] Problem with result ordering

> Hi,
>
> when I fire the following query:
> select m_id, m_u_id, m_title, m_rating from tablename where m_id in
> (26250,
> 11042, 16279, 42197, 672089);
>
> I will get the same results in the same order, as in in the next query:
> select m_id, m_u_id, m_title, m_rating from tablename where m_id in
> (11042,42197,672089,26250,16279);
>
> I wonder, how it is possible, to retrieve the results in the same order,
> as
> queried in the list. The listed IDs are from an application outside the
> database.
>
> Version is PostgreSQL 8.2.1
>
> Has anyone an idea, how to do this, while PostgreSQL knows nothing about
> hints, like oracle does?
>
> THX,
> Thorsten
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>