The SQL Core spec does not include a definition for TOP or LIMIT, and neither does Oracle. EntitySpaces simulates TOP for Oracle as close as possible with ROWNUM, but as you point out, Oracle does have its peculiarities, and it will not produce the same results as SQL Server's TOP or MySQL's LIMIT in all cases.
ES2008 does support sub-queries:
Code:
Dim cqSub As New CustomerQuery("cSub")
cqSub.Where(cqSub.Manager.Equal(1))
cqSub.OrderBy(cqSub.DateAdded.Descending)
Dim cq As New CustomerQuery("c")
cq.es.Top = 1
cq.From(cqSub).As("sub")
Dim collection As New CustomerCollection()
collection.Load(cq)
LastQuery:
Code:
SELECT *
FROM (
SELECT *
FROM "HIERARCHICAL"."Customer" cSub
WHERE cSub."Manager" = :Manager1
ORDER BY cSub."DateAdded" DESC) sub
WHERE ROWNUM <= 1
The preferred approach is to use one of the new functions from the SQL OLAP extensions. Oracle supports ROWNUMBER OVER(), and EntitySpaces implements it in the paging mechanism for Oracle and SQL Server. I think the following approach will not only be more readable than the sub-query, but will give you better performance:
Code:
Dim collection As New CustomerCollection()
collection.Query.es.PageNumber = 1
collection.Query.es.PageSize = 1
collection.Query.Where(collection.Query.Manager.Equal(1))
collection.Query.OrderBy(collection.Query.DateAdded.Descending)
collection.Query.Load()
LastQuery:
Code:
WITH "withStatement" AS (
SELECT "Customer".*, ROW_NUMBER() OVER(
ORDER BY "DateAdded" DESC) AS ESRN
FROM "HIERARCHICAL"."Customer"
WHERE "Manager" = :Manager1)
SELECT "withStatement".* FROM "withStatement"
WHERE ESRN BETWEEN 1 AND 1
ORDER BY ESRN ASC
David Neal Parsons
www.entityspaces.net