Oracle - filter sequal issue

Wilder1626
Wilder1626 used Ask the Experts™
on
Hello all

I have a problem with a SQL.

I have this SQL bellow that i use to pull resources from a table.

The thing is that in TL_RESOURCE_TRLR A i may have multiple A.TRLR_TYPE_ID based on A.EFFECTIVE

So i only want to extract the most updated effective date per TRLR_TYPE_ID and not all the effective dates even the old one. Just the very last one

How can i update this?

Thanks again for your help


  SELECT   DISTINCT A.CARRIER_ID,
                    A.EFFECTIVE,
                    A.TRLR_TYPE_ID,
                    A.CURRENT_NUM_VEHICLES
    FROM   TL_RESOURCE_TRLR A, CARRIER_EQUIP B
   WHERE       A.CARRIER_ID = B.CARRIER_ID(+)
           AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
           AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE  OF THE OUTER JOIN
ORDER BY   A.CARRIER_ID ASC;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try this:

select carrier_id, effective, trlr_type_id, current_num_vehicles
from
(
SELECT   A.CARRIER_ID,
                    A.EFFECTIVE,
                    A.TRLR_TYPE_ID,
                    A.CURRENT_NUM_VEHICLES,
row_number() over(partition by carrier_id order by carrier_id, effective desc) myrn
    FROM   TL_RESOURCE_TRLR A, CARRIER_EQUIP B
   WHERE       A.CARRIER_ID = B.CARRIER_ID(+)
           AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
           AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE  OF THE OUTER JOIN
)
where myrn=1;


or a simple max?

SELECT   DISTINCT A.CARRIER_ID,
                    MAX(A.EFFECTIVE),
                    A.TRLR_TYPE_ID,
                    A.CURRENT_NUM_VEHICLES
    FROM   TL_RESOURCE_TRLR A, CARRIER_EQUIP B
   WHERE       A.CARRIER_ID = B.CARRIER_ID(+)
           AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
           AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE  OF THE OUTER JOIN
group by carrier_id, trlr_type_id, current_num_vehicles
ORDER BY   A.CARRIER_ID ASC;
Wilder1626

Author

Commented:
Hi slightwv

Let me look at this, i'm not having the same result in both SQL
slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I might have fat-fingered when I retyped your SQL.

If you can provide some sample data and expected results, I can create an actual working example.
Wilder1626

Author

Commented:
Hi again,

do you know what, i like the second one.

smaller but exactly what i need.

Thanks again for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial