Limit rows in descending order
25 October, 2012
Hello,
I was wondering if there is a way or a workaround to show the LAST n number of rows if the row limit has been reached.
For example, i have a row limit set to 1000. I set the date filter between first and last day of the month, and the expected resultset is over 1000 rows. Currently the report shows the first 1000 rows from the beginning of the month with message "The max row limit has been reached. Data may have been truncated.". This is despite my date column being set to sort descending by date - the report shows first 1000 rows matching the filter and sorts them in descending order. What can i do so that the last 1000 rows are returned?
Thank you in advance!
I was wondering if there is a way or a workaround to show the LAST n number of rows if the row limit has been reached.
For example, i have a row limit set to 1000. I set the date filter between first and last day of the month, and the expected resultset is over 1000 rows. Currently the report shows the first 1000 rows from the beginning of the month with message "The max row limit has been reached. Data may have been truncated.". This is despite my date column being set to sort descending by date - the report shows first 1000 rows matching the filter and sorts them in descending order. What can i do so that the last 1000 rows are returned?
Thank you in advance!
Hi Eugene,
Unfortunately there is no way to include the last row, or return the last 1000 results.
An enhancement request has been created (TASK ID = 120419) which will be considered for a future release.
However in the meantime, you will need to either remove the 1000-row limit, or use a filter to limit the results.
Apologies for the inconvenience.
Regards,
David
Unfortunately there is no way to include the last row, or return the last 1000 results.
An enhancement request has been created (TASK ID = 120419) which will be considered for a future release.
However in the meantime, you will need to either remove the 1000-row limit, or use a filter to limit the results.
Apologies for the inconvenience.
Regards,
David
Thank you David! Looking forward to the enhancement!
Hi Eugene,
you could achieve what you want with a free-hand sql report with the use of Common Table Expressions and Windowing functions.
here's an example below based on a vanilla user table. I'm using SQL server syntax which means you would have to update it to whatever you are using.
The row_number function (= windowing function) numbers he rows returned by the report in descending creation date order, which you can use to get the last 50 rows of your record-set.
Cheers,
Christophe.
you could achieve what you want with a free-hand sql report with the use of Common Table Expressions and Windowing functions.
here's an example below based on a vanilla user table. I'm using SQL server syntax which means you would have to update it to whatever you are using.
The row_number function (= windowing function) numbers he rows returned by the report in descending creation date order, which you can use to get the last 50 rows of your record-set.
Cheers,
Christophe.