Get the first (sorted) match of second table in SQL Server?

Question

I'm sure this is easier than I'm making it, but I have two tables :

Item               Descriptions

itemID (PK)        index (PK)
                   itemID
                   Description

Now, there are multiple descriptions per itemID ... but I want to get the description with the lowest index number (the first one) PER item

I found some implementations of similar problems using Limit, but SQL SERVER doesn't have it. If someone could throw me a solution with explanation I'd be grateful. I tried using Top(1) but that gives me 1 result total... i need 1 result per Item :-/

Answer 1

but I want to get the description with the lowest index number (the first one) PER item

Try this:

WITH CTE
AS
(
   SELECT
     index, 
     itemId, 
     Description,
     ROW_NUMBER() OVER(PARTITION BY itemId
                       ORDER BY index) AS RN
   FROM tablename
)
SELECT index, itemId, Description,
FROM tablename
WHERE RN = 1;