Paging results by ROW_NUMBER is inefficient.

[syocy]

It seems that there are no functions equivalent of LIMIT and OFFSET in HRR. I found I can page results by using ROW_NUMBER in HRR, but this is a inefficient way in DB2 and PostgreSQL (http://use-the-index-luke.com/sql/partial-results/window-functions).

Although LIMIT clause is not standard SQL, some of SQL query builders written in Haskell have way to limit results (Persistent LimitTo and Opaleye limit).

I suppose that HRR philosophy is to support only standard SQL. My suggestion is to implement FETCH FIRST clause declared in SQL:2008. This is a way to realize LIMIT by standard SQL.

[khibino]

Do you want to join tables which result is limited by FETCH FIRST clause? If you does not want it, you can use relationalQuery' instead of relationalQuery with custom suffix words like below

fooLimited n = relationalQuery' fooRel [FETCH, FIRST, fromString $ show n, ROWS, ONLY]

[syocy]

Thank you very much for your advice. My present goal is achieved by your method. :)

[khibino]

Closing. When users want to compose limited queries, this issue may be reopened.