SQL Server uses the open window function over () to group statistics

This is a common face test, which is often used in practical projects.

Requirements: To find product categories for the group, the most expensive product information in each group.

The implementation process is as follows:

Declare @t table (ProductID   int, ProductName varchar (), ProductType varchar (), price int)  --test data Insert @t SEL  ECT 1, ' name1 ', ' P1 ', 3 union ALL Select 2, ' name2 ', ' P1 ', 5 union ALL Select 3, ' Name3 ', ' P2 ', 4 union ALL Select 4, ' Name4 ', ' P2 ', 4 --One way: Find the most expensive value in each group, and then find the price equal to this value in each group-disadvantage: To make a join select t1.* from    @t T1   Join (select ProductType,    max (Price) Price from    @t    Group by ProductType) T2 on T1. ProductType = t2. ProductType  where T1. Price = t2. Price  ORDER BY ProductType-procedure two: Use Over (), calculate the statistics, and then directly filter the result set. --over () allows the function (including aggregate functions) to be output with the line.  ; with CTE as (SELECT *, max (price) over (partition by (ProductType)) Maxprice from   @t) Select Productid,productna  Me,producttype,price from CTE where price = Maxprice  ORDER by ProductType--over () The syntax is: Over ([patition by] <order by >). It is important to note that over () is preceded by a function, and if it is an aggregate function, then order by cannot be used together. Another common scenario for--over () is for pagination with row_number ().

SQL Server uses the open window function over () to group statistics