How to use min() together with other column in SQL

Question

I am building an application consisting of an SQL backend and several microservices that communicate with this backend. I am trying to get as little data as possible from the database over the wire so I am trying to fetch the exact records so I don't have to start looping in the application logic.

So this is the source table :

OrderID	Topic	hierarchyLevel	Responsible Person	Status
1234	A	0	Jason	Open
1234	A	0	Carol	Open
1234	A	1	Jeff	Open
1234	A	2	Alina	Open
1234	A	3	Eileen	Open
1234	B	0	John	Closed
1234	B	1	Danny	Open
1234	B	1	Celine	Open
1234	B	2	Corry	Open
1234	B	3	Logan	Open
1234	C	0	Jason	Closed
1234	C	1	Annisa	Open
1234	C	2	Cedric	Open
1234	D	0	Peter	Closed
1234	D	1	Joanna	Closed
1234	D	2	Freeke	Open
1234	E	0	Carol	Closed
1234	E	1	Cynthia	Closed
1234	E	2	Deidra	Open

Based on the "orderID" as input parameter for the query , I should get the for every topic the next persons in line , so with the lowest HierarchyLevel number for that topic, that has status "Open". So every topic can return multiple times as long as the returned records have the lowest possible value in "HierarchyLevel" and the status is "Open".

So I would expect this as output for the stored procedure :

OrderID	Topic	hierarchyLevel	Responsible Person	Status
1234	A	0	Jason	Open
1234	A	0	Carol	Open
1234	B	1	Danny	Open
1234	B	1	Celine	Open
1234	C	1	Annisa	Open
1234	D	2	Freeke	Open
1234	E	2	Deidra	Open

I tried to work with min() but with no luck:

Some things I tried :

select * from mytable as a
inner join (
   select Topic, min(HierarchyLevel) as min_value
   from mytable 
   group by Topic
)  t on t.Topic = a.Topic and a.HierarchyLevel = min_value and a.OrderID = @OrderID and Status = 'Open'



select * from mytable as a
inner join (
   select Topic, Status ,min(HierarchyLevel) as min_value 
   from mytable 
    group by Topic , Status HAVING Status = 'Open' 
)  t on t.Topic = a.Topic and a.HierarchyLevel = min_value and a.OrderID = @OrderID and a.Status = 'Open'

None with the desired result. Could anyone guide me in the right direction?

Thank you very much.

Answer 1

One straight-forward solution is to use a correlated aggregate using exists:

select * 
from t
where exists (
    select * from t t2
    where t2.OrderId = t.OrderId 
        and t2.Topic = t.Topic 
        and t2.Status = 'Open'
    group by t2.OrdeRId, t2.Topic
    having Min(t2.hierarchyLevel) = t.hierarchyLevel
);

Demo DB<>Fiddle

Result:

Answer 2

Lookup RANK() OVER (...). I guess something like:

SELECT OrderID, Topic, hierarchyLevel, Responsible_Person, Status
FROM (
    SELECT OrderID, Topic, hierarchyLevel, Responsible_Person, Status
         , RANK() OVER (PARTITION BY OrderID, Topic
                        ORDER BY hierarchyLevel) AS rnk
    FROM t
    WHERE Status = 'Open' 
) AS T 
WHERE rnk = 1;

Answer 3

One way is to check if all the records with open status intersect with said records also having the lowest heirarchy_level per order_id and topic

select order_id, topic, heirarchy_level, responsible_person, status
from t
where status='Open'
intersect
select order_id, topic, min(heirarchy_level) over (partition by order_id, topic), responsible_person, status
from t
where status='Open'