Clash Royale CLAN TAG#URR8PPP

Select a Row with Max of CreatedOn group by a criteria

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)


Below is the Result of the Query:

As you can see there are similar containers with 2 rows
I want to select the row with the latest createdon.

Please help editing my query.

4 Answers
4

i think a sub-query and join would help you

select iC.* from INVENTORY_CONTAINER IC inner join
inner join
(
select IC.Container,max(IC.CreatedOn) as CreatedOn
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
) t on IC.Container=t.Container and IC.CreatedOn=t.CreatedOn


If you are using SQL Server try below query:

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


You need to write one more subquery to your query.

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'
and IC.CreatedOn=
(select max(IC1.CreatedOn) from INVENTORY_CONTAINER IC1
where IC1.container=IC.container));
group by IC.Container
having count(IC.Container) >= 2 ;


I am assuming that the query which you have mentioned is syntactically correct as per your DB design.

Try the following query-:

With CTE as
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY Container ORDER BY CreatedOn desc) AS RN
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
)select * from CTE where RN=1


SQL Server

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.