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

User · Accepted Answer

Simply use min    SELECT company  workflow  MIN date   FROM workflowTable  GROUP BY company  workflow

User · Answer

SELECT company      workflow      MIN date  FROM workflowTable GROUP BY company          workflow

User · Answer

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

[sql] SQL: How To Select Earliest Row

Examples related to sql

Examples related to date

Examples related to select

Examples related to subset