Limits

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.
.

Relevant Pages

  • Re: Limits
    ... mean, as far as I know it has no basis in relational algebra, and yet ... (In Oracle 11g I am still forced to ... price are candidate keys) I can equate: ... SELECT * FROM cars ...
    (comp.databases.theory)
  • Re: OT: How are you coping with gas prices?
    ... Drivers Worldwide Feel Sting of Gas Prices By GEORGE JAHN, ... the painful price increases have led to some changes in European ... some Europeans grimace and bear it. ... Europeans tend to drive more fuel-efficient cars. ...
    (comp.sys.hp.mpe)
  • Re: von Mises Institute on Henry George
    ... >> cars is imposed. ... >> willing to pay the highest price? ... >the incentive for car suppliers to supply cars varying with the market price ... The supply curve for land is a vertical line. ...
    (sci.econ)
  • Re: von Mises Institute on Henry George
    ... >>that a land value tax will drive rents to zero and thus the land value tax ... No. Market price does not depend on the sellers' efforts, ... >cars is imposed. ...
    (sci.econ)
  • STATE OF THE HOBBY: Diecast, Trading Cards & Other Musings
    ... and the price comes down. ... Similar to what happened to the baseball and football card industry ... I chuckle when I speak with other collectors and they talk about their ... next car is released and then buy both cars to save on S/H, ...
    (rec.toys.cars)