Re: Extending my question. Was: The relational model and relationalalgebra - why did SQL become the industry standard?

"Lauri Pietarinen" <lauri.pietarinen_at_atbusiness.com> wrote in message news:3E55D843.8000007_at_atbusiness.com...
> >What happens here if the 10th and 11th ranking of employees by salary have
the
> >same salary? Should you not get a compile error if salary is not unique
> >within emp?
> >
> In that case both 10th and 11th will be included, as stated in TTM.
> In practice, if you want _exactly_ 10 values (provided there are at
> least 10 employees) you could include the key
> in the ranking, e.g.

I assumed so.
I must say that I'm disappointed that Dataphor uses RETURN as a keyword here.

> SELECT emp OVER{salary,empno} WHERE deptno=1 RETURN 10 BY {salary
> desc, empno}

the uninitiated would expect the above to RETURN (at most) 10 ROWs. Something such as

SELECT emp OVER{salary,empno} WHERE deptno=1 AND RANK{salary desc, empno} <= 10

would look better to me

> >I'm rather partial to DB2's
> > RANK() OVER ()
> >and
> > RANK_DENSE() OVER()
> >
> >for quota queries.
>
> That's interesting.... How would that previous query look like
> in DB2?
>

SELECT *
FROM (
    SELECT EMPNO, SALARY

    ,    rank() over(order by SALARY desc) as rank
    ,    dense_rank() over(order by SALARY desc) as dense_rank
    ,    row_number() over(order by SALARY desc, EMPNO) as rn

EMPNO SALARY RANK DENSE_RANK RN
------ ----------- ------ ---------- ------

000010    53000.00      1          1      1
000110    47000.00      2          2      2
000020    41000.00      3          3      3
000050    40000.00      4          4      4
000030    38000.00      5          5      5
000070    36000.00      6          6      6
000060    32000.00      7          7      7
000090    30000.00      8          8      8
000220    30000.00      8          8      9
000120    29000.00     10          9     10
000240    29000.00     10          9     11

  11 record(s) selected.

I would like it more if it allowed RANK() etc directly in the WHERE clause, but that may come in time.

In principle, ROW_NUMBER could(/should?) generate a compile time warning if it might return physical dependent values. E.g. if I had missed the EMPNO in the over() clause above. The results of RANK and DENSE_RANK however are always well defined.

> regards,
> Lauri Pietarinen
>
>
Received on Mon Feb 24 2003 - 13:44:04 CET