Thursday, July 9, 2009

DQL query similar to TOP and ROWCOUNT in SQL

If you want to restrict your query result to a selected number of records as we are doing someting similar to "select top 5 * from authors_table" or similar to ROWCOUNT.
The same criteria can be achieved in DQL using the following parameter ENABLE(FETCH_ALL_RESULTS )

select r_object_id from dm_document ENABLE(FETCH_ALL_RESULTS 5) will return the first five records

select r_object_id from dm_document ENABLE(FETCH_ALL_RESULTS 0) will return the entire results collection.

For more information can be found at : http://robineast.wordpress.com/2007/08/14/dql-hints-optimize-top/

4 comments:

  1. It works, thanks =)

    ReplyDelete
  2. Thanks. can also use
    SELECT object_name FROM dm_document ENABLE (RETURN_TOP 10);

    ReplyDelete
  3. select r_object_id from dm_document ENABLE(FETCH_ALL_RESULTS 5) will return the first five records

    it works to me! nice :)

    ReplyDelete