我需要通过在OrganizationID上分组来选择距离最小的行。以下是我在一个表格中的数据:
ID OrganisationID Distance
0 10 100
1 10 200
3 10 50
4 20 80
5 20 300
这就是我想要的结果:
ID OrganisationID Distance
3 10 50
4 20 80
这将实现:
SELECT t1.*
FROM yourTable t1
LEFT JOIN yourTable t2
ON (t1.OrganisationID = t2.OrganisationID AND t1.Distance > t2.Distance)
WHERE t2.OrganisationID IS NULL;
sqlfiddle演示
请注意,如果有多行具有最低距离重复,则会同时返回
编辑:
如果,正如你在评论中所说,只需要一列和最小距离,你可以用MIN和GROUP BY轻松完成:
SELECT city, MIN(distance)
FROM table2
GROUP BY city;
sqlfiddle演示
p.s.我看到了你之前删除的问题,并用一个不同的东西回答了它(我想告诉你,由于你在WHERE子句中有organizationID,你可以做:SELECT TOP 1…order by Distance DESC),但如果你需要更多的organizationID
这就是解决方案:
SELECT ID ,D.*
FROM <TABLE> INNER JOIN( SELECT OrganisationID 'OR',MIN(Distance) DI
FROM <TABLE>
GROUP BY OrganisationID) D
ON D.DI=<TABLE>.Distance
测试:
CREATE TABLE #T
(
ID INT,
OrganisationID INT,
Distance INT
)
INSERT INTO #T
SELECT 0,10,100
UNION ALL
SELECT 1,10,200
UNION ALL
SELECT 3,10,50
UNION ALL
SELECT 4,20,80
UNION ALL
SELECT 5,20,300
SELECT ID ,D.*
FROM #T INNER JOIN( SELECT OrganisationID 'OR',MIN(Distance) DI
FROM #T
GROUP BY OrganisationID) D
ON D.DI=#T.Distance
DROP TABLE #T