The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
830243044
accept rate: 14%

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:

×238
×12

question asked: 25 Sep '13, 08:29

question was seen: 577 times

last updated: 25 Sep '13, 11:10