RAC - Rank This!

Sql Server 2005 Ranking functions vs. Rac Ranking

Principal players:
Rac v2.33 running on  (more info on Rac here)
MS Sql Server 2005 sp1

In general any abstraction for t-sql is good abstraction. And
the Sql-99 analytic ranking functions are indeed a welcome
addition to Sql Server 2005. There has been universal approval
and all kinds of favor has be accorded them from the Sql Server
community. But the history of sql is the history of the gang
that can't shoot straight. And the gang tends to always be a
day late and a dollar short. So did sql and MS show itself to
be all it can be with the new ranking functionality? Is the
Sql Server model superior or inferior to well..the 'RAC' model
of ranking. Two logically different approaches. Think of this
as Fox Sql, fair and balanced..you decide :)

Here is the general specification of a rank construct from Bol:

RANK_TYPE ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Excuse me if I ask just who thinks this stuff up? Perhaps they
should get off what their on or get back on what their off. It's
painful for those not steeped in sql speak and clearly doesn't 
capture the logical/conceptual meaning of the construct. How many
of you first looked at this spec and wondered just what is the basis
for the rank in the first place? So allow me to express it based on
common sense:

RANK_TYPE (For Column(s)/[ASC | DESC]) OVER TABLE <Optional:[PARTITION BY Column(s)]>

Res ipse loquitor - Now the thing speaks for itself. 

The rank is based on a column. To be fair this is what the sql
standard meant when they expressed the idea of the 'utility'
of a construct to express the ageless sql of a rank in a
subquery:

(select count(*) 
       from t1
         where t1.column<=t2.column) as Rank
         
The meaning of the ordering of the column and whether or not the
rank is obtained within groups/partitions or over the entire table
is clear.

Now here is the concept of a rank in Rac expressed in a similar way:

RANK_TYPE (For PARTITION Columns) OVER TABLE Optional ORDER BY Columns

Res ipse loquitor - Now the thing speaks for itself. 

Here the object of a rank is a partition! The sort order of the partition
are the column(s) of the partition but an optional sort order can be
defined with columns independent of the partition column(s). This is
a logically different concept than the sql rank. That an sql rank thru
a column(s) cannot encapsulate a rank predicated on partition(s) or
a structure if you will is because there is no guarantee that a column(s)
exists to capture or enumerate such a rank. In other words a single
subquery, the sql model, may not be adaquate. The underlying sql code
necessary to capture the rank may take the form of nested subqueries,
certainly more complicated than the sql rank single subquery.

The underlying Rac model for capturing ranks over partitions can be
seem here where I first proposed creating 'virtual groups':
    microsoft.public.sqlserver.programming 
    Mon, Jul 5 1999 
    'Difficult SQL Question'
    Name: 'Trysql' (but signed by yours truely -:)  
    The thread can be seen here. 
    
Of course a partition can be conceptually decomposed to simple column(s)
so the sql rank is encapsulated within the logical model of the Rac rank.

Here is an example that elucidates the two different ranking models.
(The data is extrapolated from an example of a Rac solution which can be seen here).

create table ranktest (ID tinyint primary key,ROWID tinyint,TYPE tinyint,POS tinyint,STRING char(1))
insert ranktest values(1,1,1,1,'1')
insert ranktest values(2,1,1,2,'2')
insert ranktest values(3,1,2,3,'-')
insert ranktest values(4,1,2,4,'%')
insert ranktest values(5,1,2,5,'=')
insert ranktest values(6,1,2,6,'B ')
insert ranktest values(7,1,1,7,'3')
insert ranktest values(8,1,1,8,'1')
insert ranktest values(9,1,1,9,'2')
insert ranktest values(10,1,2,10,'A')
insert ranktest values(11,2,2,1,'D')
insert ranktest values(12,2,2,2,'C')
insert ranktest values(13,2,2,3,'M')
insert ranktest values(14,2,1,4,'6')
insert ranktest values(15,2,1,5,'5')
insert ranktest values(16,2,2,6,'T')
insert ranktest values(17,2,2,7,'Y')
insert ranktest values(18,2,2,8,'W')
insert ranktest values(19,2,2,9,'R')
insert ranktest values(20,2,1,10,'6')
insert ranktest values(21,2,1,11,'7')

select * from ranktest

ID   ROWID TYPE POS  STRING 
---- ----- ---- ---- ------ 
1    1     1    1    1
2    1     1    2    2
3    1     2    3    -
4    1     2    4    %
5    1     2    5    =
6    1     2    6    B
7    1     1    7    3
8    1     1    8    1
9    1     1    9    2
10   1     2    10   A
11   2     2    1    D
12   2     2    2    C
13   2     2    3    M
14   2     1    4    6
15   2     1    5    5
16   2     2    6    T
17   2     2    7    Y
18   2     2    8    W
19   2     2    9    R
20   2     1    10   6
21   2     1    11   7

We want a dense rank for the partition ROWID,TYPE. Although ID 
and POS are redundant they imply a meaningfulness to the order
of rows both over the table and of TYPE within ROWID.

The Rac model can be represented conceptually as:

DENSE_RANK (For PARTITION {ROWID,TYPE}) OVER TABLE (ORDER BY ID)

or

DENSE_RANK (For PARTITION {ROWID,TYPE}) OVER TABLE (ORDER BY ROWID,POS)

Rac does not offer the autonomy of ranking specifications as in
sql. Partitions are defined by the ordinal position of columns
in @rows. In the absence of optional sorting of the rows using
@rowsort, the partition is ordered by the @rows specification
from left to right. With @rowsort the partition is orderd by the
@rowsort column(s). The Rac ranking parameter @rowindicators returns
a dense rank. 

The key parameters to obtain the dense rank are:

@rows='ROWID & TYPE & POS & STRING',
@rowsort='ID' or @rowsort='ROWID & POS'
@rowindicators='TYPE{DENSE_RANK}'

The @rows specification implies the needed partition. 
The 'TYPE' in the @rowindicators argument 'TYPE{DENSE_RANK}'
is the end point of the partition to obtain the dense rank for.
In other words, all partitions as defined in @rows will be included
for this dense rank up to and including TYPE. Therefore 'TYPE'
implies the inclusion 'ROWID' and the partition of 'ROWID,TYPE'
will be used for the rank. Any number of ranks can be defined
in a Rac execute but they will all be based on the implied partition
in @rows from left to right. A rank based on row_number() is
included via @rowcounters and uses the partition 'ROWID,TYPE'.
The name of the dense rank is within the tides, ie. '{DENSE_RANK}'.
The name of the row_number rank is 'ROW NUMBER'.

Rac executes:

Exec Rac
@transform='_dummy_',
@rows='ROWID & TYPE & POS & STRING',
-- Order the partitions (@rows).
@rowsort='ID',
@pvtcol='Report Mode',@defaultexceptions='dumy',
@from='ranktest',@defaults1='y',@rowbreak='n',@racheck='y',
@counterdatatype='tinyint',
-- DENSE_RANK() using PARTITION BY ROWID,TYPE ORDERED BY ID
@rowindicators='TYPE{DENSE_RANK}',
-- ROW_NUMBER() using PARTITION BY ROWID,TYPE ORDERED BY ID
@rowcounters='TYPE{ROW_NUMBER}'

OR

Exec Rac
@transform='_dummy_',
@rows='ROWID & TYPE & POS & STRING',
-- Order the partitions (@rows).
@rowsort='ROWID & POS'
@pvtcol='Report Mode',@defaultexceptions='dumy',
@from='ranktest',@defaults1='y',@rowbreak='n',@racheck='y',
@counterdatatype='tinyint',
@rowindicators='TYPE{DENSE_RANK}',
@rowcounters='TYPE{ROW_NUMBER}'

Result

ROWID  TYPE POS  STRING DENSE_RANK ROW_NUMBER 
------ ---- ---- ------ ---------- ---------- 
1      1    1    1      1          1
1      1    2    2      1          2
1      2    3    -      2          1
1      2    4    %      2          2
1      2    5    =      2          3
1      2    6    B      2          4
1      1    7    3      3          1
1      1    8    1      3          2
1      1    9    2      3          3
1      2    10   A      4          1
2      2    1    D      1          1
2      2    2    C      1          2
2      2    3    M      1          3
2      1    4    6      2          1
2      1    5    5      2          2
2      2    6    T      3          1
2      2    7    Y      3          2
2      2    8    W      3          3
2      2    9    R      3          4
2      1    10   6      4          1
2      1    11   7      4          2

With the sql rank the question becomes is there a column(s)
that encapsulates the bi-directional nature of TYPE within ROWID.
Sadly there is not. They only column appropriate for the sql rank
is the DENSE_RANK itself! In other words, the sql rank cannot
be stated:

DENSE_RANK() OVER (PARTITION BY ROWID,TYPE ORDER BY ?) as DENSE_RANK 

or is redundant and a non-solution:

DENSE_RANK() OVER (PARTITION BY ROWID ORDER BY TYPE) as DENSE_RANK 

Any of the sql ranking functions, not matter how they are forumulated,
will not reflect the meaning of the data. This is especially seen
by comparing the sql ROW_NUMBER() with the one from Rac :

select ID,ROWID,TYPE,POS,STRING,
ROW_NUMBER() OVER (PARTITION BY ROWID order by TYPE) as [TYPE ROW_NUMBER],
ROW_NUMBER() OVER (PARTITION BY ROWID,TYPE order by POS) as [POS ROW_NUMBER] 
from ranktest
order by ID

ID   ROWID TYPE POS  STRING TYPE ROW_NUMBER POS ROW_NUMBER RAC ROW_NUMBER 
---- ----- ---- ---- ------ --------------- -------------- -------------- 
1    1     1    1    1      1               1              1
2    1     1    2    2      2               2              2
3    1     2    3    -      7               1              1
4    1     2    4    %      8               2              2
5    1     2    5    =      9               3              3
6    1     2    6    B      10              4              4
7    1     1    7    3      3               3              1
8    1     1    8    1      4               4              2
9    1     1    9    2      5               5              3
10   1     2    10   A      6               5              1
11   2     2    1    D      9               1              1
12   2     2    2    C      10              2              2
13   2     2    3    M      11              3              3
14   2     1    4    6      1               1              1
15   2     1    5    5      2               2              2
16   2     2    6    T      5               4              1
17   2     2    7    Y      6               5              2
18   2     2    8    W      7               6              3
19   2     2    9    R      8               7              4
20   2     1    10   6      3               3              1
21   2     1    11   7      4               4              2

Again the ranks do not reflect the meaning of the data when the
rank is dependent on a column instead of a logical partition.

While the sql rank is indeed a welcome abstraction they could
have done a much better job. By using such a low level of
abstraction many clear and easy solutions to non-trivial problems
remain lost to most of the user community. This is unfortunate. 
While there is a certain quality in quantity sql never seems to
truely show that the sum is greater than the parts. And for those
who build database systems as well as those that use them and
who frown on abstraction, the burden is on them to tell the rest
of us why. Less can be very much more.



Obtaining sql ranks with Rac.

This is intended to show how Rac obtains equivalent sql ranks.

This example use the adventureworks query in Bol shown under
'Ranking Functions (Transact-SQL)' 
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e7f917ba-bf4a-4fe0-b342-a91bcf88a71b.htm

An additional rank for ROW_NUMBER within PostalCode is added.

SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
--Rank for LastName,FirstName Partitioned by Postcode    
    ,ROW_NUMBER() OVER (PARTITION BY a.PostalCode ORDER BY c.LastName,c.FirstName) AS 'PART. Row Number'
    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
    
FirstName  LastName   Row Number PART. Row Number Rank Dense Rank SalesYTD    PostalCode      
---------- ---------- ---------- ---------------- ---- ---------- ----------- --------------- 
Maciej     Dusza      1          1                1    1          4557045.05  98027
Shelley    Dyck       2          2                1    1          5200475.23  98027
Linda      Ecoffey    3          3                1    1          3857163.63  98027
Carla      Eldridge   4          4                1    1          1764938.99  98027
Carol      Elliott    5          5                1    1          2811012.72  98027
Jauna      Elson      6          1                6    2          3018725.49  98055
Michael    Emanuel    7          2                6    2          3189356.25  98055
Terry      Eminhizer  8          3                6    2          3587378.43  98055
Gail       Erickson   9          4                6    2          5015682.38  98055
Mark       Erickson   10         5                6    2          3827950.24  98055
Martha     Espinoza   11         6                6    2          1931620.18  98055
Janeth     Esteves    12         7                6    2          2241204.04  98055
Twanna     Evans      13         8                6    2          1758385.93  98055    


Sql Rank                                   Rac Rank
ROW_NUMBER() OVER (ORDER BY a.PostalCode)  Rac by default computes a ROW_NUMBER over the
                                           table in the sort order implied by @rows or
                                           explicitly specified in @rowsort. It is 
                                           referenced by @tablecounter if there is no
                                           @select or by rd in a select statement.

RANK() OVER (ORDER BY a.PostalCode)        @rowruns='dumy^rank() within tablecounter^(dumy){PostalCode}'
                                           Rac does not have a default for RANK() as accounting
                                           for ties is seldomed used or needed. But it can be obtained 
                                           easily using a form of the Rac running sums parameter
                                           @rowruns. 
DENSE_RANK() OVER (ORDER BY a.PostalCode)  PostalCode{[Dense Rank]}
                                           A trivial case of a column (sql) and a partition (Rac)
                                           having the same semantics.
NTILE(4) OVER (ORDER BY a.PostalCode)      Rac has no direct equivalent expression. It can be computed
                                           using a combination of Rac options but it seems seldom
                                           called for.

The sql rank:

ROW_NUMBER() OVER (PARTITION BY a.PostalCode ORDER BY c.LastName,c.FirstName)


is equivalant to the Rac @rowcounters='PostalCode{[PART. Row Number]}'. The implied
sort order in @rows, PostalCode,LastName,FirstName, gives the Rac @rowcounters
specification the same semantics as this ROW_NUMBER.

Regardless of the order columns are specified in @rows, the data can
be presented/manipulated at will via the @select parameter.

Here is the Rac version which returns the same result as the adventureworks query.

Exec Rac
@transform='_dummy_',
@rows='a.PostalCode & c.LastName & c.FirstName & s.SalesYTD',
@pvtcol='Report Mode',
@from='Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID',
@where='TerritoryID IS NOT NULL AND SalesYTD <> 0',
@rowbreak='n',@defaults1='y',@racheck='y',
@tablecounter='y',
@rowindicators='PostalCode{[Dense Rank]}',
@rowcounters='PostalCode{[PART. Row Number]}',
@rowruns='dumy^rank() within tablecounter^(dumy){PostalCode}',
@select='select FirstName,LastName,rd as [Row Number],[PART. Row Number],
            1*runs as RANK,[Dense Rank],SalesYTD,PostalCode 
               from rac
                 order by rd'

RAC - Are you Coordinated?

Is not the concept of a 'coordinate' a clear and powerful
idea? The entire world is mapped by the coordinates of
latitude and longitude. You can pinpoint any place in the
world given its coordinates. A primary key on a table is
a coordinate. The key identifies preciously the row in
the table. If you can create the coordinates of any 
information you have created the ability to preciously
identify anything about it. Especially when the information
is data.

A frequent problem is finding a particular occurrence of a
'some thing' in a string. The 'some thing' is frequently
a number. The problem becomes finding the Nth occurrence
of a number, any number. For example, given the string:

'ASF--124 RW!==RXT34146$==PFS773044Y -JPFK*5435DAD '

extract the 3rd occurrance of a number, ie. '773044'.

Often the solution to this is to build a forrest of string
functions. But it is too easy to get lost in such a
forrest:) But, if we can map the string with coordinates
the problem and solution become clear, precise and
simple. So how do we create coordinates and what
form do they take? Well we can transpose the string to
rows and create a set of coordinates for each row, 
ie. each character. The rows/characters correspond to
the string as we read it from left to right. Once the
string is transposed we'll create 2 coordinates. The
first one is simply an indicator for whether or not
the character is a digit. This makes sense since
conceptually we only care if a character is a digit
(part of a number) or is a non-digit, not part of
a number. We're not concerned about individual
characteristics of any character, right?:) Now the
2nd coordinate, well that's going to be a bit of magic.
And this bit of magic is going to take the form of
a 'rank'. I'm now going to put RAC to use to develop
a very particular but easy rank.

I'll be using:
MS Sql Server 2005 sp1
RAC v2.34 beta
For details on RAC go here.

First some test data:

create table RT (rowid int primary key,messystr varchar(30))
insert RT values(1,'12-%= 312 ')
insert RT values(2,'YTW$152&OP8393&K734680UF104')
insert RT values(3,'624/!/53RF274UT56')
insert RT values(4,'342')
insert RT values(5,'ORDER')
insert RT values(6,'\\8AApEFS30902$1')
insert RT values(7,'4#7/&91A56G7VW37@&&&&')

select * from RT

rowid       messystr                       
----------- ------------------------------ 
1           12-%= 312 
2           YTW$152&OP8393&K734680UF104
3           624/!/53RF274UT56
4           342
5           ORDER
6           \\8AApEFS30902$1
7           4#7/&91A56G7VW37@&&&&

We're going to start by splitting each string character
by character per rowid. We're also going to create the
first coordinate, a new column, that's 1 if the character
is a digit and 2 for everything else. This is column 'type'.
Column pos is the position (from left to right) of the
character ('str1') in 'messystr'. Column 'rd' is generated
by Rac and is an ascending integer over the whole table
that follows the sort order by 'rowid' and 'pos'. The
result is saved in table ##t1.

Exec Rac
@split='[position]',
@rows='rowid & [position]', 
@pvtcol='messystr',@rank='str',
@splitby='1', -- Splits a string by N consecutive positions.
@from='RT',@defaults1='y',@rowbreak='n',@racheck='y',
@select='select rd,1*rowid as rowid,1*position as pos,str1,
         case when ascii(str1) between 48 and 57 then 1 else 2 end as type
         into ##t1
           from rac
             order by rd'

Here is the result for 'rowids' 1 and 6.

select * from ##t1 where rowid in (1,6)

rd          rowid       pos         str1 type        
----------- ----------- ----------- ---- ----------- 
1           1           1           1    1
2           1           2           2    1
3           1           3           -    2
4           1           4           %    2
5           1           5           =    2
6           1           6                2
7           1           7           3    1
8           1           8           1    1
9           1           9           2    1
10          1           10               2
63          6           1           \    2
64          6           2           \    2
65          6           3           8    1
66          6           4           A    2
67          6           5           A    2
68          6           6           p    2
69          6           7           E    2
70          6           8           F    2
71          6           9           S    2
72          6           10          3    1
73          6           11          0    1
74          6           12          9    1
75          6           13          0    1
76          6           14          2    1
77          6           15          $    2
78          6           16          1    1

Now for our 2nd coordinate, a rank. The 'type' varies
within 'rowid' ordered by 'rd'. We want a rank that
increments only when 'type' changes within 'rowid'
and consequently stays the same within 'type'. Within
a 'rowid' the rank can range from 1 to N. When the
'rowid' changes the rank resets to 1. This kind of
rank is obtained using the Rac @rowindicators parameter.

Exec Rac
@transform='_dummy_',
@rows='rowid & type & pos & str1',
@rowsort='rd',
@pvtcol='Sql*Plus',@defaultexceptions='dumy',
@from='##t1',@defaults1='y',@rowbreak='n',@racheck='y',
@rowindicators='type{typerank}',@counterdatatype='int'

Here is the result for rowid's 1 and 6.

rowid  type pos  str1 typerank    
------ ---- ---- ---- ----------- 
1      1    1    1    1   1st occurrence @1
1      1    2    2    1   1st occurrence @1
1      2    3    -    2
1      2    4    %    2
1      2    5    =    2
1      2    6         2
1      1    7    3    3
1      1    8    1    3
1      1    9    2    3
1      2    10        4
6      2    1    \    1
6      2    2    \    1
6      1    3    8    2    1st occurrence @2
6      2    4    A    3
6      2    5    A    3
6      2    6    p    3
6      2    7    E    3
6      2    8    F    3
6      2    9    S    3
6      1    10   3    4
6      1    11   0    4
6      1    12   9    4
6      1    13   0    4
6      1    14   2    4
6      2    15   $    5
6      1    16   1    6

With 'type' and 'typerank' we now have the coordinates
of any occurrence of a number. If you examine the
table closely you should be able to come up with the
formulation of it:) Ok here it is:

The Nth occurrence of a number has coordinates:

'type'=1 and

'typerank' in ((2*N)-1 , 2*N)
 
1st occurrence
(2*1)-1 , 2*1 : in (1,2)
 
2nd occurrence
(2*2)-1 , 2*2 : in (3,4)
 
3rd occurrence
(2*3)-1 , 2*3 : in (5,6)
 
4th occurrrence
(2*4)-1 , 2*4 : in (7,8)

Note that 'typerank' is an 'or' not an 'and'. You are
guaranteed to have a number in one but not both:)
For example, for the 1st occurrence the number will
have a 'typerank' of 1 if it is not proceeded by 
non-digits (see 'rowid' 1 above). If non-digits come
first the 1st occurrence is at 'typerank' 2 (see 'rowid'
6 above). The same logic applies to any Nth occurrence.

So now we can put a variable filter in RAC that accepts
the computed coordinates for 'typerank' for any particular
occurrence we want/pass to it (a form of macro substitution
for stored procedure parameter arguments). The filter
is contained in the @wherecounters parameter.

For example, we'll get the rows for the 2nd occurrence
of a number.

declare @N int,@N1 int,@N2 int
-- Only the value of @N is needed. 
set @N=2
set @N1=(@N*2)-1
set @N2=@N*2
--
Exec Rac
@transform='_dummy_',@user1=@N1,@user2=@N2, 
@rows='rowid & type & pos & str1',
@rowsort='rd',
@pvtcol='Sql*Plus',@defaultexceptions='dumy',
@from='##t1',@defaults1='y',@rowbreak='n',@racheck='y',
@rowindicators='type{typerank}',@counterdatatype='int',
--
-- Use coordinates to filter rows. @N1 and @N2
-- will be substituted for @user1 and @user2.
--
@wherecounters='type=1 and typerank in(@user1,@user2)',
@select='select rd,1*rowid as rowid,1*pos as pos,str1
         into ##t2
         from rac
          order by rd'

select * from ##t2

rd          rowid       pos         str1 
----------- ----------- ----------- ---- 
7           1           7           3
8           1           8           1
9           1           9           2
21          2           11          8
22          2           12          3
23          2           13          9
24          2           14          3
44          3           7           5
45          3           8           3
72          6           10          3
73          6           11          0
74          6           12          9
75          6           13          0
76          6           14          2
81          7           3           7

Now we only have to concatenate the rows within 'rowid'
to return the number. This is very simple with Rac.

Exec Rac
@transform='Max(str1) as str',
@rows='rowid',
@pvtcol='pos',
@from='##t2',
@defaults1='y',@racheck='y',
@concatenate='str',@stringname='number',
@separator='',@cutpvt='y'

rowid  number 
------ ------ 
1      312
2      8393
3      53
6      30902
7      7

And there you have it. Of course the whole process can 
be streamlined.

So are you going to get coordinated or are you going to
continue to go around in circles? :)


To paraphrase Gordon Geiko from 'Wall Street':
'Abstraction is good, it clarifies and purifies...'
It allows you to solve a problem, an end instead of
solving a means and then an end. There is something
to be said about qualitatively different solutions.
Precisely what I am unsure, but I think it something
very good.

We need more abstraction in data management. That is
why I am an advocate of Dataphor and the D4 language.
That Rac doesn't have a lot of company is not a good
thing, it's a bad thing. That a company like MS has
a vested interest in not fostering abstraction is a
bad thing. The industry continues to flounder in its
own sea of code and contradictions. That is not a good thing.

Find your way to show another way.

(Added 12/20/2008
 Note to see a much easier solution to this problem using Dataphor see:
'Extracting numbers from a string with Dataphor')

RAC - Sealed with a KISS

Here's a recent question posted in an MS Sql Server newsgroup:

I am trying to formulate a SELECT statement to query the following
table:

CREATE TABLE dbo.PART_TABLE(
  PART_ID VARCHAR(12) NOT NULL
, JOB_NUMBER VARCHAR(12) NOT NULL
, ENTRY_DATE DATETIME NOT NULL DEFAULT GETDATE()
, TYPE_CODE VARCHAR(12) NOT NULL
, PRIMARY KEY (PART_ID, JOB_NUMBER, ENTRY_DATE)
)


What I want to do is to return all rows of the table where the
TYPE_CODE is the same for two consecutive ENTRY_DATEs, for each
PART_ID.

The PART_IDs will not necessarily have a daily entry, so the
'consecutive' dates may actually be a few days apart.

In other words, I want to know the rows where a PART_ID had the same
TYPE_CODE on the chronologically previous ENTRY_DATE as the next.

So, if my table contained:

-------------------------------------------------
1111       30       01/06/06      ZXY
2222       40       01/06/06      ZXY
2222       35       03/06/06      ABC
1111       23       03/06/06      ZXY
3333       87       02/06/06      ABC
---------------------------------------------------

the query would return:
1111       30       01/06/06      ZXY
1111       23       03/06/06      ZXY

as the same TYPE_CODE was used on two consecutive ENTRY_DATEs for
PART_ID '1111'.

A number of very bright sql programmers proposed solutions.
Unfortunately you would need to be an extremely sophisticated
sql expert to understand them. There is a point at which the 
code completely obfuscates the logic of the solution.
Now I'm an advocate of KISS (keep it simple stupid). This was
one of the major motivations for developing the RAC utility.
Questions like the one above can be kissed very easily if
framed properly. At its simplist this problem can be solved
by sorting and computing a few ranks based on the sort.

Here is some same data:

insert PART_TABLE values('1111',30,'01/06/06','ZXY')
insert PART_TABLE values('2222',40,'01/06/06','ZXY')
insert PART_TABLE values('2222',35,'03/06/06','ABC')
insert PART_TABLE values('1111',23,'03/06/06','ZXY')
insert PART_TABLE values('3333',87,'02/06/06','ABC')
insert PART_TABLE values('1111',21,'03/10/06','ZXY')
insert PART_TABLE values('2222',23,'03/21/06','ABC')
insert PART_TABLE values('3333',24,'02/16/06','HUH')
insert PART_TABLE values('2222',27,'01/04/06','ABC')

Here is the data sorted in RAC without any other processing.
Sorting by the sequence of PART_ID, ENTRY_CODE and TYPE_CODE
encapsulates the whole solution.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE & TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER 
------- --------- ---------- ---------- 
1111    ZXY       01/06/06   30
1111    ZXY       03/06/06   23
1111    ZXY       03/10/06   21
2222    ABC       01/04/06   27
2222    ZXY       01/06/06   40
2222    ABC       03/06/06   35
2222    ABC       03/21/06   23
3333    ABC       02/06/06   87
3333    HUH       02/16/06   24

Now we're going to put a counter/rank on 
TYPE_CODE within PART_ID called TYPERANK.
Every time PART_ID changes or TYPE_CODE within
PART_ID changes the rank is set back to 1.
Otherwise the rank increments by 1. We're also
going to add a first/last indicator for the rank
called LASTYPE. If the rank is not the last combination
of PART_ID/TYPE_CODE it's 0, if its the last a 1.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
1111    ZXY       03/10/06   21         3        1
2222    ABC       01/04/06   27         1        1
2222    ZXY       01/06/06   40         1        1
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1
3333    ABC       02/06/06   87         1        1
3333    HUH       02/16/06   24         1        1

Now using TYPERANK and LASTYPE it's easy to pick off
the 1st 2 consecutive rows for PART_ID and TYPE_CODE.
The @wherecounters parameter makes this simple test.
Conceptually all this is done with a single pass thru
the data.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}',
@wherecounters='LASTYPE=0 or TYPERANK=2'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1

Kiss/RAC which also stands for less is more.

Hopefully this has been helpful. Or are we back to 'huh'?:)

Don't Use RAC

I told you I like bs.Of course you should you use RAC.And not only because it's not free. RAC is a utility for MS Sql Server.Check it out at: www.rac4sql.net RAC was way ahead of its time and remains so:) I find the people against RAC more interesting than those who use it.One of the major criticisms against it is that it undermines the learning curve of the user.By solving pivoting and other messy problems the user will be robbed of an important coding education.Yeah and we're on a noble goal of building a democracy in Iraq!When most users were fumbling around, like a monkey trying to screw a football, attempting to get dynamic crosstabs and do complex ranking along came RAC.Instead of embracing it,as in the meaning of 'utility',it met resistance.And this resistance was the code word 'education'.What it really did was rob a bunch of nitwits from furtile areas of showing their coding ability.RAC was a threat to sql programming newsgroup participation.Ego,ego,ego.Since there doesn't appear to be any bitching about 'administrative' utilities,this would appear to be a case of the classic double standard.One set of rules for administration and other set for data manipulation.Meaning that administration has a lower place in the food chain.This should make DBA's feel good:) I'll revisit this topic later with emphasis on olap/sql-99 and the usual whipping boy of dynamic sql .