SQL Tutorial - Window Functions - Ranking with Group By
15.2 هزار بار بازدید -
7 سال پیش
-
Another fantastic SQL Tutorial brought
Another fantastic SQL Tutorial brought to you by BeardedDev.
T-SQL Querying
https://www.amazon.com/gp/product/073...
T-SQL Fundamentals
https://www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/073...
If you are new to working with Windows Functions check out this video: SQL Tutorial - Window Functions
If you want to find out more about the Group By clause and working with aggregate functions check out this video: SQL TUTORIAL - GROUP BY, HAVING, Aggr...
If you are interested in finding out more about ranking functions available in SQL Server check out this video: SQL Tutorial - Window Functions - Ran...
In this tutorial we explore ranking functions as part of Window Functions using the Group By clause within SQL Server.
In this example we go through how to rank customers based on the sum of the orders they have placed with us.
The window function example also covers the difference between using Rank and Dense_Rank.
SQL
SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY Total DESC
SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
, RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS dnse
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY rnk
T-SQL Querying
https://www.amazon.com/gp/product/073...
T-SQL Fundamentals
https://www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/073...
If you are new to working with Windows Functions check out this video: SQL Tutorial - Window Functions
If you want to find out more about the Group By clause and working with aggregate functions check out this video: SQL TUTORIAL - GROUP BY, HAVING, Aggr...
If you are interested in finding out more about ranking functions available in SQL Server check out this video: SQL Tutorial - Window Functions - Ran...
In this tutorial we explore ranking functions as part of Window Functions using the Group By clause within SQL Server.
In this example we go through how to rank customers based on the sum of the orders they have placed with us.
The window function example also covers the difference between using Rank and Dense_Rank.
SQL
SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY Total DESC
SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
, RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS dnse
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY rnk
7 سال پیش
در تاریخ 1396/08/18 منتشر شده
است.
15,271
بـار بازدید شده