我有这样的表:
CREATE TABLE #Test
(
ParentID int,
DateCreated DATETIME,
ItemNo int
)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (1,'2008-10-01 00:00:00.000',0)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (1,'2008-10-01 00:00:00.000',1)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (1,'2008-05-01 00:00:00.000',2)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (1,'2008-05-01 00:00:00.000',3)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (2,'2008-06-01 00:00:00.000',3)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (2,'2008-06-01 00:00:00.000',4)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (2,'2008-04-01 00:00:00.000',6)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES (2,'2008-04-01 00:00:00.000',8)
我需要一种方法来选择相同parentID上具有最高ItemNo的最高DateCreated,以及是否可以在查询中使用这样的解决方案:
SELECT *
FROM #Test t
JOIN
(
If I could get maximum row here somehow that would be great
) maxt
ON t.ParentID = maxt.ParentID
JOIN SomeOtherTable sot
ON sot.DateCreated = maxt.MaxDateCreated
AND sot.ItemNo = maxt.MaxItemNo
GROUP BY
sot.Something
只是为了澄清结果应该是什么样子:
ParentID DateCreated ItemNo ParentID MaxDateCreated MaxItemNo
1, '2008-10-01 00:00:00.000' ,0 1, '2008-10-01 00:00:00.000',1
1, '2008-10-01 00:00:00.000' ,1 1, '2008-10-01 00:00:00.000',1
1, '2008-05-01 00:00:00.000' ,2 1, '2008-10-01 00:00:00.000',1
1, '2008-05-01 00:00:00.000' ,3 1, '2008-10-01 00:00:00.000',1
2, '2008-06-01 00:00:00.000' ,3 2, '2008-06-01 00:00:00.000',4
2, '2008-06-01 00:00:00.000' ,4 2, '2008-06-01 00:00:00.000',4
2, '2008-04-01 00:00:00.000' ,6 2, '2008-06-01 00:00:00.000',4
2, '2008-04-01 00:00:00.000' ,8 2, '2008-06-01 00:00:00.000',4
如果您需要创建日期的最大值和此日期的最大ItemNo:
select ParentId,
DateCreated as MaxDateCreated,
ItemNo as MaxItemNo
from
(select PArentID,DateCreated,ItemNo,
Row_Number() OVER (PARTITION BY ParentID
ORDER BY DateCreated DESC,
ItemNo Desc) as RN
from #Test
) t3
where RN=1
SQLFillde演示
UPD-
为了得到问题中提到的结果,你应该加入#TEST,比如:
SELECT *
FROM Test t
JOIN
(
select ParentId,
DateCreated as MaxDateCreated,
ItemNo as MaxItemNo
from
(select PArentID,DateCreated,ItemNo,
Row_Number() OVER (PARTITION BY ParentID
ORDER BY DateCreated DESC,
ItemNo Desc) as RN
from test
) t3
where RN=1
) maxt
ON t.ParentID = maxt.ParentID
SQLFiddle演示
SELECT
DateCreated, ItemNo, ParentID,
MAX(DateCreated) over (PARTITION BY ParentID) MaxDateCreated,
MAX(itemNo*case when rn = 1 then 1 end) over (PARTITION BY parentid) MaxItemNo
FROM
(
SELECT DateCreated, ItemNo, ParentID,
row_number() over (PARTITION BY parentid order by DateCreated desc, ItemNo desc) rn
FROM #test
) a
更简单的解决方案:使用max(concat(first_column,second_column))
这个想法是插入两列,然后使用最大进行排序
示例:select max(concat(first_column,second_column)) from table