Re: insert statement using order by

From: <rtproffitt_at_my-deja.com>
Date: Fri, 05 Nov 1999 18:38:17 GMT
Message-ID: <7vv86p$293$1@nnrp1.deja.com>

Stephen,

I believe Thomas was on the right track when he described using In-line tables. The problem is that ORDER BY cannot be used earlier than Oracale 8i (8 or 8i ???). I am routinely using rownum in some List-Of-Values algorithms for Forms, and it works fine...I get around the Order By problem by using Group By with enough columns to guarantee single row uniqueness.

As long as you remember that the physical order cannot be guaranteed, and that all query statements which involve rownum will need to account for the sort order, then you'll be fine using In-line tables, I think...

In your example, two users request two different selects at the same time...Therefore you cannot use a solution where the table is physically sorted...it must be a virtual solution, such as: two different kinds of select statements....

Consider the general case:
Select rownum as MyRow, a,b,c,d
From
  (select a,b,c,d
   from tbla (...,tblb, tblc..etc)
   Where ...etc...
   Group by

      a,b,x,y...etc.  enough to be unique to row
                      (order by in Oracle 8)

   ) WrkTbl
Where rownum>= beginning_value
And rownum <= ending_value
Order by d,e,f ...optional rearragement of output

The inner table gathers the data, sorted (in this case grouped) a particular way...
For user A, it would be

   Group by Name, b,c,d... etc to make uniqueness For user B, it would be

   Group by Description, b,c,d...etc.

The outer select adds rownum, which is the rownum of the sorted inner result thus: User A's data would be (name, description):

   1, Allen     ,Roof
   2, Billings  ,House
   3, Smith     ,Garage

while
User B's data would be (name, description)

   1, Smith,    ,Garage
   2, Billings  ,House
   3, Allen     ,Roof

Your trick will be how to organize this into an easy view or query for all users to use...

I don't know if this is faster or slower than Thomas' method... just written differently..

Good Luck,
Robert Proffitt
Beckman Coulter
Brea California

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 12:38:17 CST