sql select min, return value from different column in same row with grouping

Question

I want the OrderType for the min(Date) for each Name. So, I want this:

Name    Date        OrderType
Alex    1/1/2014    Direct
Alex    9/15/2014   Distributor
Cindy   6/4/2014    Distributor
John    5/8/2014    Direct
John    2/14/2014   Distributor

to return this:

Name    Date        OrderType
Alex    1/1/2014    Direct
Cindy   6/4/2014    Distributor
John    2/14/2014   Distributor

Answer 1

We can get row number based on the date for each [Name] and pick the least date record.

SELECT [T].* 
FROM (  
    SELECT [Name]
         , [DATE]
         , [OrderType]
         , ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date]) AS [seq]
    FROM [TableA]
) AS [T]
WHERE [T].[seq] = 1

Answer 2

I think you need select the min date per person then join back to the original table to get the type for that row.

Assuming your table is called tab and each person only had one order per date (otherwise the question is impossible) then something like:

Select t.name, t.date, t.ordertype
From tab t,
     ( select min (i.date) date, i.name from tab i group by i.name) t2
Where t.date = t2.date
  And t.name = t2.name

Sorry I work mainly with mysql and Oracle not tsql so it is generic sql syntax.