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:
SELECT TOP 500 ColumnName, COUNT(*) OVER() AS NumberOfRows FROM Owner.ViewName ORDER BY ColumnName

Thanks,

Jim

asked 25 Sep '13, 08:29

J%20Diaz's gravatar image

J Diaz
1.2k384665
accept rate: 10%

1

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:))

(25 Sep '13, 08:37) Volker Barth

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 ...

permanent link

answered 25 Sep '13, 11:10

trexco's gravatar image

trexco
336111423
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×273
×12

question asked: 25 Sep '13, 08:29

question was seen: 2,089 times

last updated: 25 Sep '13, 11:10