select TOP 1 * from Sales.SalesOrderDetail
select TOP 1 * from Sales.SalesOrderHeader



SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS [Row Number],
SalesOrderDetailID AS [Detail ID],
ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID

SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS [Row Number],
SalesOrderDetailID AS [Detail ID],
ProductID
FROM Sales.SalesOrderDetail
ORDER BY [Row Number]



SELECT SalesOrderID,
RANK() OVER 
(ORDER BY (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = O.SalesOrderID))
AS Rank
FROM Sales.SalesOrderHeader AS O




SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS [Row Number],
SalesOrderDetailID AS [Detail ID],
ProductID
FROM Sales.SalesOrderDetail
WHERE [Row Number] BETWEEN 200 and 300
ORDER BY SalesOrderDetailID

WITH Rows
AS (
SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS [Row Number],
SalesOrderDetailID AS [Detail ID]
FROM Sales.SalesOrderDetail
)
SELECT 
O.SalesOrderDetailID AS [Detail ID],
O.ProductID,
Rows.[Row Number]
FROM Sales.SalesOrderDetail AS O
JOIN Rows on Rows.[Detail ID] = O.SalesOrderDetailID
WHERE Rows.[Row Number] BETWEEN 100 AND 200


SELECT SalesOrderID, 
ROW_NUMBER() OVER ( Partition By SalesOrderID Order By SalesOrderDetailId) 
AS [Line Item],
100 * (LineTotal)
/
SUM(LineTotal) OVER (Partition BY SalesOrderID)
AS [Percent of sale],
CarrierTrackingNumber
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID


SELECT SalesOrderID, 
ROW_NUMBER() OVER ( Partition By SalesOrderID Order By SalesOrderDetailId) 
AS [Line Item],
100 * (LineTotal)
/
(SELECT SUM (LineTotal)
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID
)
AS [Percent of sale],
CarrierTrackingNumber
FROM Sales.SalesOrderDetail O
ORDER BY SalesOrderID











SELECT SalesOrderID,
SUM(UnitPrice * OrderQty),
Count(*)
FROM Sales.SalesOrderDetail GRoup by SalesOrderID
ORDER BY SalesOrderID

Select SalesOrderID,
(UnitPrice * OrderQry)
/
SUM(UnitPrice * OrderQty) OVER (Partition By SalesOrderID),
FROM Sales.SalesOrderDetail


select mAX(DueDate) from sales.salesorderheader


SELECT SalesOrderID,
RANK() OVER (ORDER BY DueDate) AS [Ship Priority],
DueDate
FROM Sales.SalesOrderHeader
ORDER BY duedate


SELECT SalesOrderID,
DENSE_RANK() OVER (ORDER BY DueDate) AS [Ship Priority],
DueDate
FROM Sales.SalesOrderHeader
ORDER BY duedate

select count(*) From sales.SalesOrderHeader


SELECT SalesOrderID,
NTILE(5000) OVER (ORDER BY DueDate) AS [Ship Tile],
DueDate
FROM Sales.SalesOrderHeader
ORDER BY duedate



SELECT SalesOrderID, 
DENSE_RANK() OVER (ORDER BY
(SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID)) AS [Line Item Rank],
(SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Line Item Count]
FROM Sales.SalesOrderHeader O


SELECT O.SalesOrderID, DENSE_RANK() OVER
(
PARTITION BY (
SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail)
ORDER BY (
SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
))
AS [Rank]
FROM Sales.SalesOrderHeader AS O


/*
This uses the AdventureWorks database.

The following query produces incorrect results,
it seems that the COUNT and SUM aggregates to
not work properly. 

Note that what this query is attempting to do
is DENSE_RANK SalesOrderID's from the SalesOrderDetail
table, but parition them by the number of line items
that they contain. In the end the ranks for all of
the SalesOrderID's that have a 1 line item should be
1 though N, for all of the SalesOrderID's that have
2 line items should be 1 though N and so on

Note that it also outputs the line item count and
total for reference to see it it is ranking
correctly.

Notice that many of the items have a
Line count of 0, which cannot be the case.

The BOL says that the PARTITION BY clause must
be followed by a value_expression, but does not
specify what a value_expression is. The only
limitation that it puts on the value expression
is that it may only reference actual table
columns, not aliases.

If the following query is incorrect I would
expect to get a syntax error rather than
the results it produces.


SalesOrderID Rank                 Line Count  Total
------------ -------------------- ----------- ---------------------------------------
62370        1                    0           NULL
74192        1                    0           NULL
64474        1                    0           NULL
53611        1                    0           NULL
74461        1                    0           NULL
70677        1                    0           NULL
61543        1                    0           NULL
51782        1                    0           NULL
70757        1                    0           NULL
54504        1                    0           NULL
62705        1                    0           NULL
60355        1                    0           NULL
49140        1                    0           NULL


*/

SELECT O.SalesOrderID, DENSE_RANK() OVER
(
PARTITION BY (
SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID)
ORDER BY (
SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID
))
AS [Rank],
(SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Line Count],
(SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Total]
FROM Sales.SalesOrderHeader AS O
ORDER BY [Line Count], [Rank]

/* 
Here is the same query, but with the 
PARTITION BY clause removed. It, of course,
produces differnt results but the 
aggregate functions SUM and COUNT 
work properly

SalesOrderID Rank                 Line Count  Total
------------ -------------------- ----------- ---------------------------------------
51782        1                    1           1.374000
52031        2                    1           2.290000
52532        2                    1           2.290000
52682        2                    1           2.290000
52786        2                    1           2.290000
53084        2                    1           2.290000
53629        2                    1           2.290000
53786        2                    1           2.290000
53787        2                    1           2.290000
53886        2                    1           2.290000
54206        2                    1           2.290000
54596        2                    1           2.290000



*/

SELECT O.SalesOrderID, DENSE_RANK() OVER
(
ORDER BY (
SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID
))
AS [Rank],
(SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Line Count],
(SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Total]
FROM Sales.SalesOrderHeader AS O
ORDER BY [Line Count], [Rank]




SELECT O.SalesOrderID,
(SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Line Count],
(SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = O.SalesOrderID) AS [Total]
FROM Sales.SalesOrderHeader AS O
ORDER BY [Line Count], [Total]






SELECT SalesOrderID, COUNT(SalesOrderDetailID) AS [Count],
SUM(LineTotal) AS [TOTAL]
 FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY [Count]



SELECT SalesOrderID, 
ROW_NUMBER() OVER
(
PARTITION BY SalesOrderID
ORDER BY LineTotal
) [Row Number],
LineTotal,
SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID, LineTotal


SELECT min(SalesOrderID) From Sales.SalesOrderDetail


Select SalesOrderID, dbo.MySum(LineTotal) FROM AdventureWorks.Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID


Select DISTINCT SalesOrderID, dbo.MySum(LineTotal) 
OVER(PARTITION BY SalesOrderID)
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID



Select SalesOrderDetailID,
SUM(LineTotal)
OVER(PARTITION BY SalesOrderID)
AS [Total],
SalesOrderID
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID




Select DISTINCT SalesOrderID,
SUM(LineTotal)
OVER(PARTITION BY SalesOrderID)
AS [Total]
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID

SELECT SalesOrderID,
SUM(LineTotal) AS [Total]
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID


SELECT
SalesOrderDetailID,
SalesOrderID,
SUM(LineTotal) OVER (PARTITION BY SalesOrderID)
AS [Total]
FROM Sales.SalesOrderDetail
 


SELECT
SalesOrderDetailID,
SUM(LineTotal) OVER (PARTITION BY SalesOrderID)
AS [Total],
COUNT(*) OVER (PARTITION BY ProductID)
AS [Prod Count]
FROM Sales.SalesOrderDetail
 


SELECT SalesOrderID,
SUM(LineTotal) AS [Total]
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID




