|
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 Example 2: Thanks, Jim |
|
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 ... |
Is it a requirement to use one query? I could imagine (but don't know!) the engine could optimize a separate "SELECT COUNT(*)" statement better than as part of example 2...
FWIW: Here's a FAQ with helpful details whether SELECT COUNT(*) is slow or not... (yes, I'm aware you don't want it to be slow:))