Re: Is relational theory irrelevant?

From: Carl C. Federl <cfederl_at_yahoo.com>
Date: 14 Nov 2003 09:19:52 -0800
Message-ID: <db76e830.0311140919.268c4ed7_at_posting.google.com>

Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com> wrote in message news:<3FB4906F.10708_at_atbusiness.com>...
> Carl C. Federl wrote:
>
> >The SQL standard now includes the concept of windowing functions in
> >addition to the grouping functions. The SQL statement to Rank by
> >salary within each department would be on the order of:
> >
> >select employeeId , employeeName, SalaryAmt,DepartmentId
> >, rank() over (partition by departmentId order by salaryAmt) as
> >SalaryRank
> >from employees
> >
> OK, thanks for the info. I will have to take a closer look at this.
> BTW what does the above statement produce?
>
> Lauri
To the original SQL statment, more of the windowing functions have been included to show more of the capabilities: select employeeId , SalaryAmt,DepartmentId , rank() over (partition by departmentId order by salaryAmt desc) as SalaryRank
, dense_rank() over (partition by departmentId order by salaryAmt desc) as SalaryDenseRank
, PERCENT_RANK() over (partition by departmentId order by salaryAmt desc) as SalaryPctRank
, CUME_DIST() over (partition by departmentId order by salaryAmt desc) as SalaryCumDist
, ROW_NUMBER() over (partition by departmentId order by salaryAmt desc) as SalaryRN
, AVG(salaryAmt) over (partition by departmentId ) as SalarySum from employees
order by DepartmentId, SalaryRank

Here is the result with the columns seperated by the & character. To view easily, using Word, convert to a word table. With Excel, use the data, "text to columns" function.

EmployeeId&SalaryAmt&DepartmentId&SalaryRank&SalaryDenseRank&SalaryPctRank&SalaryCumDist&SalaryRN&SalarySum
1&130000&1&1&1&0&0.2&1&103200
4&103000&1&2&2&0.25&0.4&2&103200
2&100000&1&3&3&0.5&0.6&3&103200
3&98000&1&4&4&0.75&0.8&4&103200
5&85000&1&5&5&1&1&5&103200
7&100000&2&1&1&0&0.75&1&95000
8&100000&2&1&1&0&0.75&2&95000

9&100000&2&1&1&0&0.75&3&95000
6&80000&2&4&2&1&1&4&95000

The ranking function computes the ordinal rank of a row within the window partition based on the window ordering of those rows. Based on ties, there are two variations that determine the ordinal rank:   When RANK is used, the rank of a row R is defined as one plus the number of rows that precede R and are not peers of R.   When DENSE_RANK is used, the rank of a row R is the count of the rows preceding and including R that are distinct with respect to the window ordering.

From the example result, look at the values for departmentId 1 versus 2.

Other windowing functions are PERCENT_RANK, CUME_DIST and ROW_NUMBER. All grouping functions (sum, average, ...) can also be used as window functions. Received on Fri Nov 14 2003 - 18:19:52 CET