SQL：如何选择最早的行

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):

这是通过SQL（特别是T-SQL版本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:

有任何想法吗？我无法弄清楚这一点。我尝试使用嵌套选择返回最早的托盘日期，然后在WHERE子句中设置它。如果只有一家公司，这很有用：

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!

但如果该表中有多个公司，这显然不起作用。任何帮助表示赞赏！

3 个解决方案

#1

Simply use min()

只需使用min（）

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

#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():

在这种情况下，一个相对简单的GROUP BY可以工作，但一般来说，如果有其他列无法排序，但是您希望它们与它们关联的特定行，则可以使用它们联接回详细信息密钥的所有部分或使用OVER（）：

Runnable example (Wofkflow20 error in original data corrected)

可运行的示例（原始数据中的Wofkflow20错误已更正）

;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

#3

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

#1