Select Row with Max Value into join

Question

This is problably something very simple, but I'm working on this statement for almost a day and I can't get through somehow.

What I want to do, is to get for every storage place the matching parcelorder which has the youngest date.

STORAGEPLACE( ID, STORAGE_ZONE, VALUEA)

PARCELORDER (ID, SHELF, ENDTIME, VALUEB)

Storagezone and Shelf are from the same type.

There can be multiple matching storageplaces and multiple parcelorders, thats why I need that with the minimum value.

VALUEA and VALUEB are just additional values.

SELECT  p1.NAME, t1.ID, t1.ENDTIME
    FROM STORAGE_PLACE p1
    LEFT JOIN PARCELORDER t1
    ON p1.STORAGE_ZONE=t1.SHELF
    INNER JOIN( SELECT ta.ID, MIN(ta.ENDTIME) as mindate
                from PARCELORDER ta
                GROUP BY ta.ID) tb
    ON tb.ID = t1.ID and tb.mindate = t1.ENDTIME 
    WHERE p1.VALUEA = 0  AND t1.VALUEB = -1

I tried several joins, (natural, left, inner...) which all result in multiple combinations of storage_places and parcelorders.

What I have tried too is

SELECT  p1.NAME, t1.ID, t1.ENDTIME
    FROM STORAGE_PLACE p1
    LEFT JOIN PARCELORDER t1
    ON p1.STORAGE_ZONE=t1.SHELF
    INNER JOIN( SELECT DISTINCT(ta.ID), MIN(ta.ENDTIME) as mindate
                from PARCELORDER ta
                GROUP BY ta.ID) tb
    ON tb.ID = t1.ID and tb.mindate = t1.ENDTIME 
    WHERE p1.VALUEA = 0  AND t1.VALUEB = -1

SELECT  p1.NAME, t1.ID, t1.ENDTIME
    FROM STORAGE_PLACE p1
    LEFT JOIN PARCELORDER t1
    ON p1.STORAGE_ZONE=t1.SHELF
    INNER JOIN( SELECT FIRST 1 ta.ID, MIN(ta.ENDTIME) as mindate
                from PARCELORDER ta
                GROUP BY ta.ID) tb
    ON tb.ID = t1.ID and tb.mindate = t1.ENDTIME 
    WHERE p1.VALUEA = 0  AND t1.VALUEB = -1

and several combinations of them, but those only give back all the combinations of the first parcelorder.

I'm using an older firebird, so the row_number() approach as described somewhere in SELECT ONE Row with the MAX() value on a column won't work.

Answer 1

Looking at your schema it I'm guessing this is a one to many relationship with SHELF in PARCELORDER being a foreign key to STORAGE_ZONE in STORAGE_PLACE. Your query is grouping on the ID of PARCELORDER which I'm guessing is the primary key on the table so you still get one record for every record in the table.

What you want to do is group by SHELF so you only get one record per SHELF.

Try this query:

SELECT  p1.NAME, t1.ID, t1.ENDTIME
    FROM STORAGE_PLACE p1
    INNER JOIN( SELECT ta.SHELF, MIN(ta.ENDTIME) as mindate
                from PARCELORDER ta
                GROUP BY ta.SHELF) tb
    ON p1.STORAGE_ZONE=t1.SHELF 
    INNER JOIN PARCELORDER t1 ON t1.ENDTIME = tb.mindate AND t1.SHELF = tb.SHELF
WHERE p1.VALUEA = 0  AND t1.VALUEB = -1

This should work for you assuming there is only one record in PARCELORDER for a given SHELF and ENDTIME. Otherwise you may have to create a temp table with the IDs of the records with the min ENDTIME for a given SHELF.

Answer 2

It is hard for me to tell how the two tables are related, since you didn't provide that information, other than in queries that aren't working, therefore the relations we try to decipher from your queries may be incorrect as well. That said, try this below. If it doesn't work, I'll need to know more details on how the rows in the two tables are related, preferably with sample data:

with CTE_MIN_TIME_PER_SHELF as (
  select   SHELF, min( ENDTIME ) as MIN_ENDTIME
  from     PARCELORDER
  group by SHELF
)
select     p1.NAME, t1.ID, c.MIN_ENDTIME
from       STORAGE_PLACE p1
left join  CTE_MIN_TIME_PER_SHELF c
      on   c.SHELF    = p1.STORAGE_ZONE
left join  PARCELORDER t1
      on   t1.SHELF   = c.SHELF
      and  t1.ENDTIME = c.MIN_ENDTIME

I don't see where VALUEA and VALUEB are coming from for this part. Is this a Firebird thing?:

 WHERE p1.VALUEA = 0 AND t1.VALUEB= -1