During application development there are many cases in which I am interested in providing the user with the first X (TOP X) number of rows in a query as well as provide an indication that there are more or better yet that there is a specific number number of rows. We have approached this in two different ways, example 1 says there is more, example 2 says there are x rows meeting this condition. Example 2 is preferred but I am concerned about the time it will take to process.
I was wondering if anyone had guidance on which general method is preferred or if there is a better way.
Example 1: SELECT TOP 500 ColumnName, IFNULL ( (SELECT TOP 1 START AT 501 ColumnName FROM Owner.ViewName ORDER BY ColumnName), '0', '1' ) AS MoreRowsExist FROM Owner.ViewName ORDER BY ColumnName
asked 25 Sep '13, 08:29
Could do something like this, the row count would repeat on each line, but the count function itself would only fire once I believe ...
select top 500 columnname, row_cnt from tablename , (select count(*) row_cnt from tablename) as mdt ;
Where you use a derived table to get the total count and just retrieve the information from the derived table for each row returned. You could also do a count on the rows retrieved and set a flag to indicate if more rows were available. something like:
select top 500 columnname, row_cnt ,If count(*) <> row_cnt then 'Y' else 'N' endif as MoreRowsAvail ...
answered 25 Sep '13, 11:10