Select max date from table when joining multiple tables

Question

I am trying to write a query to retrieve the max dispatched date and also bringing in fields from multiple tables. Im using the fields from other tables since the table that contains the dispatched date does not contain any of the information that I am looking for. (hope that makes sense) This is the code that I am currently using and it is not working:

Here is what I was getting before I started playing with the code:

This is what I am trying to retrieve:

select a.order_num, 
LAB_USER.NAME as ASGN_TECH, 
(select max(asn_assignment.dispatched) 
from asn_assignment 
where a.for_order=asn_assignment.for_order) as "max date"
from  asn_assignment, ord_order_state a, lab_user, lab_resource, asn_assignment
where
LAB_USER.USER_ID = LAB_RESOURCE.FOR_USER
AND LAB_RESOURCE.RESOURCE_ID = ASN_ASSIGNMENT.FOR_RESOURCE
and order_num in ('800000194709',
'800000213722',
'800006513931',
'800006525705')

Tag your question with the database you are using. Sample data and desired results also help. And why aren't you using proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Dec 20 '20 at 01:54
So many problem you made. You have 2 asn_assignment table in from statement, and there's no condition for ord_order_state to relate to other table. — ElapsedSoul, Dec 20 '20 at 02:01
Add your table columns along with example data so we can at least address your issue and resolve it if possible. — Abdul Raheem Ghani, Dec 20 '20 at 07:54

score 0 · Answer 1 · answered Dec 20 '20 at 02:06

Perhaps fix it like this first while still don't catch you point:

select a.order_num, 
LAB_USER.NAME as ASGN_TECH, 
(select max(asn_assignment.dispatched) 
from asn_assignment 
where a.for_order=asn_assignment.for_order) as "max date"
from  asn_assignment, ord_order_state a, lab_user, lab_resource
where a.for_order=asn_assignment.for_order
and LAB_USER.USER_ID = LAB_RESOURCE.FOR_USER
AND LAB_RESOURCE.RESOURCE_ID = ASN_ASSIGNMENT.FOR_RESOURCE
and order_num in ('800000194709',
'800000213722',
'800006513931',
'800006525705')

Barbaros Özhan · Answer 2 · 2020-12-21T11:14:57.127

You can rewrite the query through use of DENSE_RANK() analytic function such as

SELECT order_num, asgn_tech, dispatched
  FROM
  (
    SELECT o.order_num,
           lu.name as asgn_tech,
           a.dispatched,
           DENSE_RANK() OVER (PARTITION BY o.order_num 
                                  ORDER BY a.dispatched DESC) AS dr
      FROM asn_assignment a
      JOIN ord_order_state o
        ON o.for_order = a.for_order
      JOIN lab_resource lr
        ON lr.resource_id = a.for_resource
      JOIN lab_user lu
        ON lu.user_id = lr.for_user       
     WHERE order_num IN (800000194709, 800000213722, 800006513931, 800006525705)
   )
  WHERE dr = 1

where

prefer using SQL-92 standard which contains JOIN keyword rather than former standards in which the tables are seperated by commas
properly alias tables with one or two (maybe three depending on the tables' names) letters conforming to initial letters of their names or underscore seperated substrings within the names
get rid of quotes wrapping up the values within the list after IN operator considering data type of order_num is numeric

If your database is 12c+, then you can also prefer using the below query without need of a subquery such as

SELECT o.order_num,
       lu.name as asgn_tech,
       a.dispatched
  FROM asn_assignment a
  JOIN ord_order_state o
    ON o.for_order = a.for_order
  JOIN lab_resource lr
    ON lr.resource_id = a.for_resource
  JOIN lab_user lu
    ON lu.user_id = lr.for_user       
 WHERE order_num IN (800000194709, 800000213722, 800006513931, 800006525705)
 ORDER BY DENSE_RANK() OVER (PARTITION BY o.order_num ORDER BY a.dispatched DESC)     
 FETCH NEXT 1 ROW WITH TIES

Select max date from table when joining multiple tables

2 Answers2