Re: LIMIT and ORDER BY in subqueries

As an example, I want to create a view on table mytable containing all tuples with xyz value greater than fifth greatest xyz value in descending order:

create view myview as (select * from mytable where xyz > (select xyz from mytable order by xyz desc limit 1, 4) order by xyz desc)

fredt <fredt@xxxxxxxxxxxxxxxxxxxxx> wrote:

The restrictions do not allow LIMIT or ORDER on its own where it makes no difference or may return inconsistent results. Please include an example where a subquery is not accepted and does not conform to this criteria.

 

Fred

----- Original Message -----

From: Mehdi Riahi

To: hsqldb-user@xxxxxxxxxxxxxxxxxxxxx

Sent: 01 September 2007 23:59

Subject: [Hsqldb-user] LIMIT and ORDER BY in subqueries

Hi,

Why LIMIT and ORDER BY clauses have been restricted in subqueries in 1.8.0.8. In previous versions we could use them in any subquery and now they are allowed only in IN, ALL and ANY subqueries.



thanks,

---

Got a little couch potato?
Check out fun summer activities for kids.

---

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/

---

_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

---

Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/hsqldb-user