Dataphor - SET-ing your query in ORDER

Wanna have some fun, how would you like to build a list anywhere in a query? When you work with
Dataphor you can think in terms of order in the mist of thinking in terms of sets. ☺

Here's how it works. When you Add an item to a list the operation returns an integer
indicating the ordinal position the item holds in the list. Add is therefore like any
other expression that returns a result (remember that Add adds one item after another in
sequence). Well you can place this expression anywhere you could place any other expression. 
That's it! ☺ For example, using an integer list:

var Item:=10;
var LList1:=list(Integer){}; //An empty list of type integer.
var Index:=Add(LList1,Item); //Index is of type integer, the returned type of the expression
                             //that inserts the value of 10 into the list.
                             //Note we can also use the expression: LList1.Add(Item).
select Index;    //Returns 0, the position of the 1st item in the list.
select LList1[0];//Returns 10, the value of the 1st item in the list.

Using a list with a row type:

var LList1:=list(row{OrderID:Integer,CustomerID:String,Freight:Money}){};
var Item:=row{1 OrderID,'STEVE' CustomerID,$100.50 Freight};
var Index:=Add(LList1,Item); //Index holds the value 0, the position of the 1st row 
                             //added to the list.
select Index; //Returns 0, the position of the 1st item (row) in the list.
select LList1[0];
/* Returns the 1st item in the list, the row:
OrderID CustomerID Freight 
------- ---------- ------- 
1       STEVE      $100.50 
*/

Remember that the Add operation with list adds items sequentially to the list starting at position 0.

OK, here's a contrived example query. It uses Sql Server 2005, the Northwind database and the Orders table.
We're using three lists where a list item is a row of the Orders table limited to only three columns
(one of which, OrderID, is the primary key of the table). In broad strokes we're looking at how relational
expressions access data. The list, after all, reflects how the data was accessed, the sequential order
in which rows from the table were processed depending on where we're at in the query. In this sense
we're introducing two independent ideas in a single operation, thinking in terms of sets and thinking
in terms of order (sequentially) that do not conflict with each other. ☺

The query first gets the 10 highest Freight values for all Freight values over $400. It then gets
the lowest 5 freights from the 10 highest ones.

var LList1:=list(typeof(Orders[]{OrderID,CustomerID,Freight})){};
var LList2:=list(typeof(Orders[]{OrderID,CustomerID,Freight})){};
var LList3:=list(typeof(Orders[]{OrderID,CustomerID,Freight})){};
//Note the "with {IgnoreUnsupported = 'true'}" in the query is used so you don't get a warning from the 
//dataphor compiler about sql server not supporting the list.Add operation. Obviously dataphor can't
//hand off the list operation to sql server so the operation is under dataphors control.

select
     ( 
      (
        Orders where Freight>$400 {OrderID,CustomerID,Freight} 
         add{LList1.Add(Orders[OrderID]{OrderID,CustomerID,Freight} with {IgnoreUnsupported = 'true'}) R1} 
          return 10 by {Freight desc} with {IgnoreUnsupported = 'true'}
          //return N is similar to sql server 'Select Top N..Order By'.
       ) 
         add{LList2.Add(Orders[OrderID]{OrderID,CustomerID,Freight} with {IgnoreUnsupported = 'true'}) R2}
          return 5 by {Freight}
      )       
         add{LList3.Add(Orders[OrderID]{OrderID,CustomerID,Freight} with {IgnoreUnsupported = 'true'}) R3}          
          order by {Freight};
          
/*  Query result ordered by Freight.
 R1, R2 and R3 are the ordinal positions from the three lists created from the query.
 See the individual lists below for details.
OrderID CustomerID Freight R1 R2 R3 
------- ---------- ------- -- -- -- 
11032   WHITC      $606.19 19 9  0  
10983   SAVEA      $657.54 16 8  1  
10479   RATTC      $708.95 2  7  2  
10816   GREAL      $719.78 9  6  3  
11017   ERNSH      $754.26 17 5  4  
*/

Using ToTable we can transform a list to a table where the ordinal position of the item (ie. row) in 
the list is converted to a value. Of course we could access the list with a cursor/loop and use
an indexer to get each individual item (ie. LList1[0]). Using ToTable is more straighforward for our
purposes. And remember list->in terms of position, table->in terms of column(s) value. ☺

ToTable automatically creates a column named sequence indicating the ordinal position (of the row).
We're renaming it to Index.

select ToTable(LList1) rename {sequence Index} order by {Index};      
/* List1 result ordered by the ordinal position (Index) of each row. 
   LList1 indicates rows in the query are added to the list in the order of the primary key
  (OrderID) values.
OrderID CustomerID Freight   Index 
------- ---------- --------- ----- 
10372   QUEEN      $890.78   0     
10430   ERNSH      $458.78   1     
10479   RATTC      $708.95   2     
10514   ERNSH      $789.95   3     
10540   QUICK      $1,007.64 4     
10612   SAVEA      $544.08   5     
10633   ERNSH      $477.90   6     
10634   FOLIG      $487.38   7     
10691   QUICK      $810.05   8     
10816   GREAL      $719.78   9     
10836   ERNSH      $411.88   10    
10841   SUPRD      $424.30   11    
10847   SAVEA      $487.57   12    
10897   HUNGO      $603.54   13    
10912   HUNGO      $580.91   14    
10941   SAVEA      $400.81   15    
10983   SAVEA      $657.54   16    
11017   ERNSH      $754.26   17    
11030   SAVEA      $830.75   18    
11032   WHITC      $606.19   19    
*/

select ToTable(LList2) rename {sequence Index} order by {Index}; 
/* List2 result ordered by the ordinal position (Index) of each row. 
   LList2 indicates rows are added to the list in the order of descending Freight values.
OrderID CustomerID Freight   Index 
------- ---------- --------- ----- 
10540   QUICK      $1,007.64 0     
10372   QUEEN      $890.78   1     
11030   SAVEA      $830.75   2     
10691   QUICK      $810.05   3     
10514   ERNSH      $789.95   4     
11017   ERNSH      $754.26   5     
10816   GREAL      $719.78   6     
10479   RATTC      $708.95   7     
10983   SAVEA      $657.54   8     
11032   WHITC      $606.19   9      
*/
  
select ToTable(LList3) rename {sequence Index} order by {Index};        
/* List3 result ordered by the ordinal position (Index) of each row.
   Rows are added to the list in the order of ascending Freight values. 
   The order of the list agrees with the result of the query.
OrderID CustomerID Freight Index 
------- ---------- ------- ----- 
11032   WHITC      $606.19 0     
10983   SAVEA      $657.54 1     
10479   RATTC      $708.95 2     
10816   GREAL      $719.78 3     
11017   ERNSH      $754.26 4     
*/

You can take this idea and really run with it. ☺ Use it to help explain how a query builds an expression,
how indexes actually influence table access to all kinds of list processing driven by a query. When
you combine the ability to build a list with other list operations like IndexOf, IndexOfAny and LastIndexOf
you can do some kewl things. Actually this simple introduction is just the tip of the iceberg when it
comes to combining these two types.

Oh yeah, note that you can conditionally build a list(s) within a query using a case (or if-then-else) statement.
For example:

var LList1:=list(typeof(Orders[]{OrderID,CustomerID,Freight})){};
select
 Orders where Freight>$700  
  add  //Build a list based on a further restriction on freight. 
     {
      case when Freight>$800 then LList1.Add(Orders[OrderID]{OrderID,CustomerID,Freight} with {IgnoreUnsupported = 'true'}) 
      else -1 end
      R1
     } 
       {OrderID,CustomerID,Freight,R1}
         order by {R1,OrderID};
         
/* Query result.
OrderID CustomerID Freight   R1 
------- ---------- --------- -- 
10479   RATTC      $708.95   -1 
10514   ERNSH      $789.95   -1 
10816   GREAL      $719.78   -1 
11017   ERNSH      $754.26   -1 
10372   QUEEN      $890.78   0  
10540   QUICK      $1,007.64 1  
10691   QUICK      $810.05   2  
11030   SAVEA      $830.75   3  
*/
       
select ToTable(LList1) rename {sequence Index} order by {Index};   
/*
OrderID CustomerID Freight   Index 
------- ---------- --------- ----- 
10372   QUEEN      $890.78   0     
10540   QUICK      $1,007.64 1     
10691   QUICK      $810.05   2     
11030   SAVEA      $830.75   3     
*/

Note that other operations with the list type like insert and remove don't return a value so you can't
use them in a query (yet). ☺

More to come on lists.