TOP

The Historian OLE DB Provider supports the use of the TOP predicate in a SELECT statement. With the TOP predicate, you can limit the number of rows returned to a specified number or percentage of rows. After you indicate the number of rows or percentage of rows with the TOP predicate, enter the rest of the query. Typically, you include ORDER BY in the query to sort the rows in a specified order.

When you SELECT the top number or top percentage of rows, the returned value is limited by the RowCount. For instance, say you want the top 30 percent of rows from a query that can return a possible 10,000 rows, but the RowCount is set to 1000. The percentage logic processes the 3000 rows first, then it reduces the number to 1000 rows, as specified by RowCount. The final result returns 1000 rows, even though the top 30 percent is processed first. Use a SET statement or WHERE clause to change or disable the RowCount behavior.

The following examples display how to return the top 40 rows in the ihTags table and the top 10 and top 10 percent of rows from the ihMessages table.

Example 1: Return the Top 40 Tags in Alphabetical Order

SELECT TOP 40 * FROM ihtags ORDER BY Tagname

Example 2: Return the Top 10 Most Recent Messages

SELECT TOP 10 timestamp, topic, username, messagestring FROM ihmessages WHERE timestamp<Now ORDER BY timestamp DESC

Example 3: Return the Top 10 Percent, RowCount Disabled

SET rowcount=0
SELECT TOP 10 PERCENT timestamp, topic, username, messagestring
FROM ihmessages WHERE timestamp<Now
ORDER BY timestamp DESC