Minimum value within a set (or window) within a three column table

Question

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012.

I have a table that looks like this:

I would like to retrieve a result set like this, where each category has the ID associated with the lowest order number, and also the ID associated with the highest order number:

I am looking for the TSQL select statement that would do this.

Answer 1

You could do this without the new (2012+) window function FIRST_VALUE(), using the ROW_NUMBER(). But you could also do it using OUTER APPLY:

SELECT 
    d.CategorieID,
    IDMinOrder = a.ID,
    IDMaxOrder = b.ID
FROM 
    ( SELECT DISTINCT CategorieID 
      FROM tableX
    ) AS d
  OUTER APPLY 
    ( SELECT TOP (1) a.ID
      FROM tableX AS a
      WHERE a.CategorieID = d.CategorieID
      ORDER BY a."Order" ASC
    ) AS a
  OUTER APPLY 
    ( SELECT TOP (1) b.ID
      FROM tableX AS b
      WHERE b.CategorieID = d.CategorieID
      ORDER BY b."Order" Desc
    ) AS b ;

Answer 2

Assuming this sample data:

USE tempdb;
GO

CREATE TABLE dbo.o([Order] INT, ID INT, CategoryID INT);

INSERT dbo.o([Order],ID,CategoryID) VALUES
(1,100,13), (2,103,13), (3,102,13), (4,105,13), (5,104,13), (6,101,13),
(1,201,57), (2,200,57), (3,204,57), (4,203,57), (5,206,57), (6,205,57);
GO

We can use a CTE to determine the first and last row, for each category, using ROW_NUMBER(), a window function that has existed since SQL Server 2005:

;WITH x AS 
(
  SELECT CategoryID, ID,
    mn = ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY [Order]),
    mx = ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY [Order] DESC)
  FROM dbo.o
)
SELECT CategoryID = COALESCE(mn.CategoryID, mx.CategoryID),
  [ID MinOrder] = mn.ID, [ID MaxOrder] = mx.ID
FROM x AS mn CROSS JOIN x AS mx
WHERE mn.CategoryID = mx.CategoryID
AND mn.mn = 1 AND mx.mx = 1;

Clean up:

DROP TABLE dbo.o;

Answer 3

 select categoryMinMax.category
      , tableMin.ID as [tableMin.ID] 
      , tableMax.ID as [tableMax.ID]
 from ( select category
             , min(order) as orderMin 
             , max(order) as orderMax 
         from table 
        group by category 
      ) categoryMinMax 
 join table as tableMin 
   on tableMin.category = categoryMinMax.category  
  and tableMin.order    = categoryMinMax.orderMin  
 join table as tableMax 
   on tableMax.category = categoryMinMax.category  
  and tableMax.order    = categoryMinMax.orderMax