0

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

Result Set Example

Arthur
  • 23
  • 1
  • 6
  • if it's only going to be 2 values, then a `CASE WHEN` statement would probably be easier than one of the window functions – SeanC Nov 06 '17 at 18:11
  • It will often be thousands of values in my real world implementation. – Arthur Nov 06 '17 at 18:28

1 Answers1

1

The SQL Server MIN commands accepts the OVER() clause:

SELECT MIN(s.BusinessEntityID) OVER (PARTITION BY TerritoryID) AS Min_BusinessEntityID
FROM ...
SMM
  • 2,225
  • 1
  • 19
  • 30