SQL: How To Select Earliest Row

Question

I have a report that looks something like this:

CompanyA      Workflow27     June5 CompanyA      Workflow27     June8 CompanyA      Workflow27     June12 CompanyB      Workflow13     Apr4 CompanyB      Workflow13     Apr9 CompanyB      Workflow20     Dec11 CompanyB      Wofkflow20     Dec17 

This is done with SQL (specifically, T-SQL version Server 2005):

SELECT company    , workflow    , date FROM workflowTable 

I would like the report to show just the earliest dates for each workflow:

CompanyA      Workflow27     June5 CompanyB      Workflow13     Apr4 CompanyB      Workflow20     Dec11 

Any ideas? I can't figure this out. I've tried using a nested select that returns the earliest tray date, and then setting that in the WHERE clause. This works great if there were only one company:

SELECT company    , workflow    , date FROM workflowTable WHERE date = (SELECT TOP 1 date               FROM workflowTable               ORDER BY date) 

but this obviously won't work if there is more than one company in that table. Any help is appreciated!

Answer 1

Simply use min()

SELECT company, workflow, MIN(date)  FROM workflowTable  GROUP BY company, workflow 

Answer 2

In this case a relatively simple GROUP BY can work, but in general, when there are additional columns where you can't order by but you want them from the particular row which they are associated with, you can either join back to the detail using all the parts of the key or use OVER():

Runnable example (Wofkflow20 error in original data corrected)

;WITH partitioned AS (     SELECT company         ,workflow         ,date         ,other_columns         ,ROW_NUMBER() OVER(PARTITION BY company, workflow                             ORDER BY date) AS seq     FROM workflowTable ) SELECT * FROM partitioned WHERE seq = 1