tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 24

Trong khi các truy vấn đầu tiên trở lại 16 hàng, các biệt ngữ trong truy vấn thứ hai loại bỏ các hàng trùng lặp và trở về chỉ có năm hàng độc đáo. SQL Server biệt là khác nhau từ distinctrow MS Access, mà loại bỏ các bản sao dựa trên dữ liệu trong bảng nguồn (s), không trùng lặp trong tập kết quả của câu truy vấn. | Part II Manipulating Data with Select TourName Amazon Trek Appalachian Trail Bahamas Dive Gauley River Rafting Outer Banks Lighthouses Whereas the first query returned 16 rows the DISTINCT predicate in the second query eliminated the duplicate rows and returned only five unique rows. - - SQL Server s DISTINCT is different from MS Access distinctrow which eliminates dupli- cates based on data in the source table s not duplicates in the result set of the query. Select DISTINCT functions as though a GROUP BY clause discussed in Chapter 12 Aggregating Data exists on every output column. Of course using DISTINCT is based on the query s requirements so there may be no choice just be aware that depending on the size and mix of the data there may be a performance impact. Top By definition SELECT works with sets of data. Sometimes however it s only the first few rows from the set that are of interest. For these situations SQL Server includes several ways to filter the results and find the top rows. As mentioned earlier SQL Server will return all the rows from the SELECT statement by default. The optional TOP predicate tells SQL Server to return only a few rows either a fixed number or a percentage based upon the options specified as shown in Figure 8-4. A variable can be passed to TOP . The older syntax for TOP did not include the parentheses and did not accept a variable. The newer syntax with the parentheses was introduced with SQL Server 2005 and is the best practice moving forward. TOP works hand-in-hand with ORDER BY. It s the ORDER BY clause that determines which rows are first. If the SELECT statement does not have an ORDER BY clause then the TOP predicate still works by returning an unordered sampling of the result set. The OBXKites sample database is a good place to test the TOP predicate. The following query finds the top 3 percent of prices in the price table. The price table allows each product to have multiple prices according to the effective date SELECT TOP 3