Limits

#1

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.

#2

JOG wrote:

Quote:

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

#3

JOG wrote:

Quote:

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

#4

JOG wrote:

Quote:

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

#5

JOG wrote:

Quote:

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

#6

JOG wrote:

Quote:

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

#7

JOG wrote:

Quote:

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

#8

JOG wrote:

Quote:

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

#9

JOG wrote:

Quote:

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

#10

JOG wrote:

Quote:

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