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;