doesn't raise any SQL error, even if it seems it doesn't return records in
the expected order, but I'm not sure of this, there could be some bug in
untit tests... I'll let you know.
If you need your result set in a specific sequence, you need to add an order
by clause to your final select.
The main goal of the optimizer is to get access to the data as fast as
possible. In this way it may be faster to use an index in a different order
and sort the result temporarily (if an additional order by is needed).
... and IMHO you do not need 2 sub-selects a single one should be
sufficient:
SELECT SUB.*
FROM (select a.Field1, b.Field1,
row_number() over (Order by a.Field1)
FROM A inner join B on A.X = B.Y) SUB (New_Name, Field1_Too, Rnk)
WHERE rnk >= ? AND rnk < ?
Order By Rnk;
By the way, the above query is not used neither in H2 nor in MySQL, since
these DBs support the LIMIT clause.
If you only want the first 5 (or 10 or whatever rows), you can specify FETCH
FIRST x ROWS ONLY at the end of your SELECT-Statement:
Select a.Field1, b.Field1
From A join B on a.Key = b.Key
Order By a.Field1
Fetch First 10 Rows Only;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Franco Lombardo
Gesendet: Friday, 26.10 2012 09:56
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Bug in SQL parser???
Chuck,
as I said in the ng answer, your syntax
SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by NEW_NAME) rnk FROM (select
A.field1, B.field1 /* instead of NEW_NAME here */ from A inner join B on A.X
= B.Y
) AS RES ( NEW_NAME, FIELD1_TOO ) /* name NEW_NAME here */
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?
doesn't raise any SQL error, even if it seems it doesn't return records in
the expected order, but I'm not sure of this, there could be some bug in
untit tests... I'll let you know.
By the way, the above query is not used neither in H2 nor in MySQL, since
these DBs support the LIMIT clause, but it was create to support MS SQL
Server and....DB2 LUW!!!
Thank you again!!!
Bye
Franco
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
