In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
COURSE of the MONTH
10 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle - filter sequal issue
Posted on 2012-12-20
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
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;
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
4 Comments
Active today
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;
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;
Active today
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.
If you can provide some sample data and expected results, I can create an actual working example.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+) as used in Oracle; *= =* as used in Sybase …
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month10 days, 15 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.