T-SQL adjacent field from Top or MAX date

Question

Can anyone help with this T-SQL request?

I have a table of 1000s of orders. Many are repeat orders for the same customer. I'm trying to create a query that would show many spend habits of each customer, and also the promotional code used on their first ever order. I can pick out their first order date by using Top or Max function, but how would i return just Cust and first Promotion code from table below?:

Cust    Order   Order Date  Promotion Code
JB001   df445   01/02/2014  Web32
JB001   56456   01/02/2015  Cat85
JB001   5646a   03/04/2012  Email22
JB001   ff55f   25/04/2003  Cat67
JB002   45454   28/09/2011  Cat55
JB002   65488   22/10/2011  Email31
JB002   793gg   15/05/2007  Email22
JB003   5444h   10/10/2014  Web14
JB003   gbnm2   22/10/2014  Autumn21

to give this?:

Cust    Promotion Code
JB001   Cat67
JB002   Email22
JB003   Web14

Many thanks in advance,

John

Answer 1

Use Window Function

SELECT cust,[promotion code]
FROM   (SELECT cust,
               [promotion code],
               Row_number()
                 OVER(
                   partition BY cust
                   ORDER BY [order date]) rn
        FROM   tablename)a
WHERE  rn = 1 

or use aggregate with Group by to find the min date per cust then join the result back to main table

SELECT cust,
       [promotion code]
FROM   tablename a
       JOIN(SELECT Min([order date]) or_date,
                   cust
            FROM   tablename group by cust) b
         ON a.cust = b.cust
            AND a.[order date] = b.or_date 

Answer 2

You can use row_number() to get the first order for each customer:

select cust, promotioncode, orderdate
from (select t.*, row_number() over (partition by cust order by orderdate) as seqnum
      from orders t
     ) t
where seqnum = 1;

Answer 3

You can use Window functions. Like, Max over date, partition by Cust order by rank and selecting records with rank 1.

Select Cust,PromotionCode from
 (Select Cust, PromotionCode, 
    Rank () over (partition by cust order by date)
    as rank from records )a
where a.rank=1