Re: Limits

JOG wrote:

> Does anyone else find LIMIT statements particularly irritating? I
> mean, as far as I know it has no basis in relational algebra, and yet
> I find myself using it constantly. (In Oracle 11g I am still forced to
> use the hideous ROWNUM which is even more irritating, and yet there I
> appear to be, row-numming away till the cows come home).
>
> So my question to cdt. Can LIMIT type operations, given their apparant
> utility, be framed in terms of relational algebra, and if so how
> elegantly can this be done? Off the top of my head the first approach
> I would take would be to order the relation concerned via a new
> attribute and then slice the appropriate section required... but this
> certainly doesn't seem very stylish. For example, given a relation:
> cars {model, price} (where for purposes of example, both model and
> price are candidate keys) I can equate:
>
> mysql:
> SELECT * FROM cars
> ORDER BY price LIMIT 5,10
>
> oracle:
> SELECT * FROM (SELECT * FROM cars ORDER BY price)
> WHERE ROWNUM>=5 AND ROWNUM<=10
>
> generic (well if mysql did nesting anyhow):
> SELECT R1.model, R1.price
> FROM (
> SELECT R1.model, R1.price, COUNT(1) position
> FROM cars R1, cars R2
> WHERE R1.price > R2.price
> GROUP BY model
> )
> WHERE position>=5 and position<=10
> ORDER BY position
>
> Any raise on what I have above? What then happens if price isn't
> unique and the ordering on x has two tuples with equivalent values for
> x? Meh, LIMITS and ORDERS bug me. I am forced to use them a lot in
> everyday apps and yet they seem to be very much SQL and not RM. Having
> said that I'm not losing sleep over them. Its all relative ;) Regards,
> J.

See "quota query" and "partition". Received on Fri Jul 25 2008 - 03:15:17 CEST