I would like to select the minimum ID of a partition. Kind of like how dense_rank works I'd like a unique number associated with each paritition, but I'd like that numbber to be the minimum primary key within the partition. Here is an example of what I'm after.
USE AdventureWorks2012;
GO
SELECT '276 or 289 depending on what partition' as 'Min_BusinessEntityID'
,s.BusinessEntityID
,p.LastName
,s.SalesYTD
,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode, SalesYTD DESC
The SQL Server MIN commands accepts the OVER() clause:
SELECT MIN(s.BusinessEntityID) OVER (PARTITION BY TerritoryID) AS Min_BusinessEntityID
FROM ...