Limits

From: JOG <jog_at_cs.nott.ac.uk>
Date: Thu, 24 Jul 2008 17:55:16 -0700 (PDT)
Message-ID: <06871bbc-e84c-4b96-a571-99a3e890df5b@r35g2000prm.googlegroups.com>


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. Received on Thu Jul 24 2008 - 19:55:16 CDT

Original text of this message