SQL Server 单一查询问题

Question

我一直在尝试查询一些信息，但我似乎无法提出查询。下面是表格信息和我当前的查询。

公司表:

+-----------+-----------+------------+
| CompanyID |  Address  | PostalCode |
+-----------+-----------+------------+
| a-coy     | 31 street |     111111 |
| b-coy     | 32 street |     111222 |
+-----------+-----------+------------+

修复表:

+----------+-----------+----------------+------------+----------+
| SerialNo | CompanyID | PersonInCharge | RepairDate | RepairBy |
+----------+-----------+----------------+------------+----------+
|    15000 | a-coy     | John           | 01-May-17  | Jason    |
|    15001 | a-coy     | John           | 02-May-17  | Jack     |
+----------+-----------+----------------+------------+----------+

我的目标是查询出最新的维修工程条目，但只有 1 个结果。

我目前的查询如下:

SELECT 
    c.companyid, c.address, r.repairdate, r.repairby
FROM 
    company c 
INNER JOIN 
    repair r ON c.companyID = r.companyID
WHERE 
    c.companyid = 'a-coy'

我得到以下结果:

+-----------+-----------+------------+----------+
| CompanyID |  Address  | RepairDate | RepairBy |
+-----------+-----------+------------+----------+
| a-coy     | 31 street | 01-May-17  | Jason    |
| a-coy     | 31 street | 02-May-17  | Jack     |
+-----------+-----------+------------+----------+

但我想要的结果只是具有最新维修日期的条目，它看起来完全像这样:

+-----------+-----------+------------+----------+
| CompanyID |  Address  | RepairDate | RepairBy |
+-----------+-----------+------------+----------+
| a-coy     | 31 street | 02-May-17  | Jack     |
+-----------+-----------+------------+----------+

Answer 1

SELECT TOP 1
c.companyid, 
c.address, 
r.repairdate,
r.repairby
FROM company c 
INNER JOIN repair r ON c.companyID=r.companyID
WHERE c.companyid = 'a-coy'
ORDER BY r.repairdate DESC