Flexible search find duplicates in database

Question

I have two tables - Warehouse(id) and StockLevel (id, productCode).
The store has many StockLevels.

I want to find duplicates of StockLevel for the same product in the same store.
Ex: Warehouse"Minimarket" have two entries of StockLevel for product "Apple".

For now, I have this, but I'm not sure it works correct way:

select {wh.code}, {sl.productCode}, count({sl.pk}) as cnt 
from {StockLevel as sl  
left join Warehouse as wh on {sl.warehouse}={wh.pk}}  
group by ({wh.code}, {sl.productCode})
having count({sl.pk}) > 1

| id | productCode | warehouse |
--------------------------
| 1 | "apple"     | "mini" |
--------------------------
| 2 | "apple"     | "mini" |
--------------------------
| 3 | "apple"     | "maxi" |
--------------------------
| 4 | "apple"     | "macro" |
--------------------------
| 5 | "orange"    | "mini" |
--------------------------

I want one of the first two entries (Id 1 and 2) to be selected(I do not really care which one).
Also I'm having problem - if I use this query in flexibleSearch, I can't cast it to entity (StockLevel) and adding {sl.pk} to query corrupt it.

Answer 1

If you just want the "first" row, you can do:

select w.*
from warehouse w
where exists (select 1
              from warehouse w2
              where w2.warehouse = w.warehouse and w2.code = w.code and w2.id > w.id
             ) and
      not exists (select 1
                  from warehouse w2
                  where w2.warehouse = w.warehouse and w2.code = w.code and w2.id < w.id
                ) ;