T-SQL ENHANCEMENTS FROM orders ) AS a WHERE num BETWEEN 400 AND 410 Although the preceding case looks similar to selecting the entire result- set into a temporary table, with num as a derived identity column, and doing a SELECT of the temporary table, in some cases the engine will be able to accomplish this without the complete set of rows. Besides being usable in a SELECT clause, the ranking and windowing functions are also usable in the ORDER BYclause. This gets employees partitioned by country and ranked by age, and then sorted by rank. SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age)) AS [rank] FROM ( SELECT lastname, country, DATEDIFF(yy,birthdate,getdate())AS age FROM employees ) AS a ORDER BY RANK() OVER(PARTITION BY COUNTRY ORDER BY age), COUNTRY produces lastname country age rank – – Dodsworth UK 37 1 Leverling USA 40 1 Suyama UK 40 2 Callahan USA 45 2 King UK 43 3 Fuller USA 51 3 Buchanan UK 48 4 Davolio USA 55 4 Peacock USA 66 5 You can also use other aggregate functions (either system-defined aggregates or user-defined aggregates that you saw in Chapter 5) with the OVER clause. When it is used in concert with the partitioning functions, however, you get the same value for each partition. This is shown next. there is one oldest employee age for each country SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank], MAX(age) OVER(PARTITION BY COUNTRY) AS [oldest age in country] FROM ( SELECT lastname, country,

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

Bookmark the permalink.

Comments are closed.