Home » SQL & PL/SQL » SQL & PL/SQL » Max function (PL SQL )
Max function [message #665054] Sun, 20 August 2017 16:34 Go to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
I have two history tables.

Parent table:
CASE	TYPE	SUBTYPE	INSERTED
PX-100	OP	OBS	1/1/2017
PX-100	INP	SRG	1/5/2017

Child table:
CASE	ID	CODE	INSERTED
PX-100	1	99605	1/1/2017
PX-100	1	88423	1/1/2017
PX-100	2	99222	1/10/2017
PX-100	2	44444	1/10/2017

How do I get my output to look like this?
CASE	TYPE 	SUBTYPE	ID	CODE
PX-100 	INP	SRG	1	99222
PX-100 	INP	SRG	2	44444

Re: Max function [message #665055 is a reply to message #665054] Sun, 20 August 2017 16:52 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Max function [message #665065 is a reply to message #665054] Mon, 21 August 2017 01:18 Go to previous messageGo to next message
John Watson
Messages: 7185
Registered: January 2010
Location: Global Village
Senior Member
What are your primary key and foreign key constraints?

If you provide the CREATE TABLE and INSERT statements, enclosed within [code] tags, perhaps all will become clear.
Re: Max function [message #665069 is a reply to message #665054] Mon, 21 August 2017 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And feedback in your previous topic. Did you solve your problem? How? What was the root of the problem?

Re: Max function [message #665091 is a reply to message #665054] Mon, 21 August 2017 12:54 Go to previous messageGo to next message
joy_division
Messages: 4802
Registered: February 2005
Location: East Coast USA
Senior Member
Sounds simple enough. I am going to make all assumptions because no details were given.

Join on case, use date >= 1/10/2017, use rownum and a descending order by.
Re: Max function [message #665092 is a reply to message #665091] Mon, 21 August 2017 13:05 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
This is a simplified version of my table and queries because I am looking for concept first.

I only want to return the most resent values as these are history tables with multiple records for every case.

select C.CASE, C.TYPE, C.SUBTYPE, P.CODE, MAX(C.INSERTED), MAX(P.INSERTED)
FROM CASE C
LEFT OUTERJOIN PROCEDURE P ON C.CASE = P.CASE
GROUP BY C.CASE, C.TYPE, C.SUBTYPE, P.CODE

When I use the max value, I get the max value (obviously) instead of last entry and "LAST" function does not work.


PL/SQL 12.0.1.1814
Re: Max function [message #665093 is a reply to message #665092] Mon, 21 August 2017 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And one more guy that refuses to follow the guide and show the least respect toward others. /forum/fa/4073/0/

Re: Max function [message #665095 is a reply to message #665093] Mon, 21 August 2017 13:22 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
My apologies. I am new to the forum and sql. What is it I am missing from the guide? I cannot actually share the real data or query as it contains proprietary information and PHI. I'm trying to speak in hypotheticals so that I might understand the concept of working with history tables that contain a record for every save. I am not able to pull the most recent record from both the parent and child tables in a meaningful fashion.
Re: Max function [message #665096 is a reply to message #665095] Mon, 21 August 2017 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What is it I am missing from the guide?
Read the links we gave you.

Quote:
I cannot actually share the real data
Who has asked for this?
Once more, read the links we gave you.

Quote:
I am new to the forum and sql.
This is not a valid excuse to have not read the links we gave you and messages we posted.

Re: Max function [message #665110 is a reply to message #665096] Tue, 22 August 2017 02:51 Go to previous message
quirks
Messages: 60
Registered: October 2014
Member
Hello OldDog,

first, we expect you to do everything you can to let us focus on the problem itself. That's because there are so many requests that it would be a waste of time if everyone is creating the test data for himself. So, you better provide a create script for the test data or some kind of virtual recordset:
-- Parent Table
SELECT 'PX-100' AS CASE, 'OP' AS TYPE, 'OBS' AS SUBTYPE, TO_DATE('1/1/2017', 'MM/DD/YYYY') INSERTED FROM DUAL
UNION
SELECT 'PX-100', 'INP', 'SRG', TO_DATE('1/5/2017', 'MM/DD/YYYY') FROM DUAL;

-- Child Table
SELECT 'PX-100' AS CASE, 1 AS ID, 99605 AS CODE, TO_DATE('1/1/2017', 'MM/DD/YYYY') AS INSERTED FROM DUAL
UNION
SELECT 'PX-100', 1, 88423, TO_DATE('1/1/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 99222, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 44444, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL;

With that off the table, your expected result should somehow make sense. But in your case there is a problem, because in the first record the value for ID does not match the value for CODE. So either your example is wrong or your explanation.
I guess the value for ID must be 2? I suspect that because you wrote:

OldDog wrote on Mon, 21 August 2017 13:22
I am not able to pull the most recent record from both the parent and child tables in a meaningful fashion.
Which leads me to the assumption that you extract your data from some kind of data warehouse. Usualy you don't have figure out the last valid recordset, because the join between the tables is NEVER done by natural values but by generic keys. Please watch out for these in you productive data. It might save you some headaches.

Whatever... We have to work with what you gave us. I've once seen a somehow misconcepted data structure where indeed the join was made by the natural keys. But to come over the resulting problems they flagged the last valid record. Please watch out in your productive data for such a flag. In my Answer I create this flag manually (but you should not have to do that, if so, it's bad data design).
WITH
    PARTENT_TABLE
    AS
        (SELECT 'PX-100' AS CASE, 'OP' AS TYPE, 'OBS' AS SUBTYPE, TO_DATE('1/1/2017', 'MM/DD/YYYY') INSERTED FROM DUAL
         UNION
         SELECT 'PX-100', 'INP', 'SRG', TO_DATE('1/5/2017', 'MM/DD/YYYY') FROM DUAL),
    CHILD_TABLE
    AS
        (SELECT 'PX-100' AS CASE, 1 AS ID, 99605 AS CODE, TO_DATE('1/1/2017', 'MM/DD/YYYY') AS INSERTED FROM DUAL
         UNION
         SELECT 'PX-100', 1, 88423, TO_DATE('1/1/2017', 'MM/DD/YYYY') FROM DUAL
         UNION
         SELECT 'PX-100', 2, 99222, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL
         UNION
         SELECT 'PX-100', 2, 44444, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL),
    PREPARE_PARENT_TABLE
    AS
        (SELECT CASE
               ,TYPE
               ,SUBTYPE
               ,INSERTED
               ,CASE WHEN INSERTED = MAX(INSERTED) OVER (PARTITION BY CASE) THEN 'Y' ELSE 'N' END LAST_VALID_ENTRY
           FROM PARTENT_TABLE),
    PREPARE_CHILD_TABLE
    AS
        (SELECT CASE
               ,ID
               ,CODE
               ,INSERTED
               ,CASE WHEN INSERTED = MAX(INSERTED) OVER (PARTITION BY CASE) THEN 'Y' ELSE 'N' END LAST_VALID_ENTRY
           FROM CHILD_TABLE)
SELECT PREPARE_PARENT_TABLE.CASE
      ,PREPARE_PARENT_TABLE.TYPE
      ,PREPARE_PARENT_TABLE.SUBTYPE
      ,PREPARE_CHILD_TABLE.ID
      ,PREPARE_CHILD_TABLE.CODE
  FROM PREPARE_PARENT_TABLE JOIN PREPARE_CHILD_TABLE ON (PREPARE_PARENT_TABLE.CASE = PREPARE_CHILD_TABLE.CASE)
 WHERE PREPARE_PARENT_TABLE.LAST_VALID_ENTRY = 'Y' AND PREPARE_CHILD_TABLE.LAST_VALID_ENTRY = 'Y';

I hope my post was a little bit helpful. Next time you'll be better prepared. I'd like to send you a warm welcome to our forum.

Don't hesitate to send further request but do your homework before Wink
Previous Topic: Join and group by on multiple tables
Next Topic: File compressing is not happening
Goto Forum:
  


Current Time: Fri Dec 15 04:16:04 CST 2017

Total time taken to generate the page: 0.01618 seconds