0

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.

0
3

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 ;
0
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;
0
 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.