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" 
To: 
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
>