Sql-server – Minimum value within a set (or window) within a three column table

sql serversql-server-2008-r2t-sqlwindow functions

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:

enter image description here

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:

enter image description here

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

Best Answer

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 ;
Related Question