sql - 按条件选择具有最大 CreatedOn 组的行

Question

select 
    Container, CreatedOn, * 
from 
    Inventory_container 
where 
    container in (select IC.Container
                  from INVENTORY_CONTAINER IC
                  inner join CONTAINER C on IC.Container = C.Container
                  where C.ContainerClassID = '100000011' 
                  group by IC.Container
                  having count(IC.Container) >= 2)

以下是查询结果：

如您所见，有 2 行的类似容器我想选择最新创建的行。

请帮助编辑我的查询。

标签： sqlsql-server

Answer 1

如果您使用的是 SQL Server，请尝试以下查询：

SELECT *
FROM ( 
       SELECT *, ROW_NUMBER()OVER(PARTITION BY Container ORDER BY CreatedOn) AS RowNo
       FROM INVENTORY_CONTAINER IC INNER JOIN CONTAINER C ON 
       IC.Container=C.Container
       WHERE C.ContainerClassID='100000011' 
       GROUP BY IC.Container,CreatedOn
       HAVING COUNT(IC.Container)>=2
     ) AS T
WHERE RowNo = 1