Ch.3 (Lesson 2.1) Filtering Data with TOP

Do you always care if the results are deterministic? What if your intention is for a TOP option to be in-deterministic when running without an ORDER BY? How would you construct it and what does it do?

No sometimes the intention is a random result. ORDER BY (SELECT NULL) SELECT NULL is used when syntax requires an ORDER BY but the user does not want to specify an order. Making the results truly random and no ordering is imposed.

What must be considered when returning rows where the PERCENT is used? For example returning 5.2% of the orders from the Sales.Orders table which has 830 rows. How many rows would be returned? Why?

PERCENT puts a ceiling on the resulting number if it is a decimal. It will round up the decimal always to the nearest whole number. 5.2% of the rows from 830 total rows comes to 43.16 rows. This is rounded up to 44 rows.

What is the syntax for writing TOP five percent orderid? What data type is the number % of rows? What is the range?

SELECT TOP (5) PERCENT orderid FLOAT. 0-100

What is the syntax for writing TOP five orderid? What data type is the number of rows?

SELECT TOP (5) orderid. BIGINT

What is the WITH TIES option for TOP? What does it do and what is the syntax? What must be included to use it?

WITH TIES will return all rows that are similar to the last result in the TOP option. SELECT TOP (3) PERCENT WITH TIES Must include an ORDER BY clause.

What does the TOP option do? Where does it appear in the SQL query?

Allows you to filter a requested number of rows or a % of rows based on an indicated order. The query is performed and the result is returned. Then the TOP option returns only a portion of the result query. It appears in the SELECT clause.

What two options allow you to answer questions like "Return the three most recent orders" and "return the five most expensive products" by filtering data based on a given ordering and specified number of rows?

TOP and OFFSET FETCH

Is the TOP option limited to a constant input? If not, what can be used?

TOP option can use variables or parameters.

Which option is T-SQL specific and which is standard between TOP and OFFSET FETCH?

TOP: T-SQL OFFSET FETCH: Standard

Is ORDER BY mandatory when using TOP? Why or why not?

The ORDER BY clause is not mandatory. If it is not used the return result is random. ORDER BY should be used to create deterministic results.

Given the following what would you expect from a WITH TIES result? There are 6 orders with the most current date in the database SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC

The result would return all rows that are similar to the last result of the TOP 3. So the result may be more than 3 rows.

Consider the following query: SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC Is this deterministic? Why or why not? What 2 options are available when using the TOP option to get deterministic results?

This query is not deterministic because it is ordering by an non unique column. There could be more than 3 orders within the TOP result value. Option 1: add to the ORDER BY clause a unique column. Such as the primary key orderid OPTION 2: use the WITH TIES option.