使用SQL组同时保持相同的varchar值

Question

我有一个返回两个值的查询。我想拥有最大的价值，所以我做了一组，然后MAX。但是，我有三个其他列（varchar），我希望与使用max引入的id保持一致。

例子。

OId  CId FName LName BName 

18477 110 Hubba Bubba whoa 
158  110 Test2 Person2 leee 

我要的是 OID识别符FName参数LName的BNAME

18477 110 Hubba Bubba whoa 

所以我想将它们按识别符。而且，我想保持最大的数字。我无法使用Min或Max作为FName，LName或BName，因为我希望它们成为具有所选OId的那个。我甚至不需要其他行的FName，LName和BName。

我尝试使用SELECT TOP，但只是从字面上拉一行，我需要多个。

SQL

INSERT INTO #CustomerInfoAll(FName, LName, BName, OwnerId, CustomerId) 
SELECT 
-- what goes here --(o.FirstName) AS FName, 
-- what goes here --(o.LastName) AS LName, 
-- what goes here --(o.BusinessName) AS BName, 
MAX(o.OId) AS OId, 
(r.CId) AS CId 
FROM Owner o 
INNER JOIN Report r 
ON o.ReportId = r.ReportId 
WHERE r.CId IN (SELECT CId FROM #ThisReportAll) 
AND r.Completed IS NOT NULL 
GROUP BY r.CId 
ORDER BY OId DESC; 

Answer 1

假设你有SQL Server 2005或更高：

INSERT INTO #CustomerInfoAll (FName, LName, BName, OwnerId, CustomerId) 
SELECT 
    FirstName, 
    LastName, 
    BusinessName, 
    Id, 
    CId 
FROM 
    (
     SELECT 
     Seq = ROW_NUMBER() OVER (PARTITION BY r.CId ORDER BY o.Id DESC), 
     o.Id, 
     r.CId, 
     o.FirstName, 
     o.LastName, 
     o.BusinessName 
     FROM 
     dbo.Owner o 
     INNER JOIN dbo.Report r 
      ON o.ReportId = r.ReportId 
     WHERE 
     EXISTS (-- can be INNER JOIN instead if `CId` is unique in temp table 
      SELECT * 
      FROM #ThisReportAll tra 
      WHERE r.CId = tra.CId 
     ) 
     AND r.Completed IS NOT NULL 
     GROUP BY 
     o.Id, 
     r.CId, 
     o.FirstName, 
     o.LastName, 
     o.BusinessName 
    ) x 
WHERE 
    x.Seq = 1; 

• 确实使用在所有的对象（dbo.Owner和​​）全模式名称。
• 尽可能使用半连接（EXISTS子句）或INNER JOIN而不是IN。