SQLTeam.com | Weblogs | Forums

Obtain the most recent data by joining two or more tables


#1

Hello..
Please bear with me as I am new to all this.

can someone assist me in figuring out the most appropriate method to obtain the most recently entered "NOTES" against the corresponding ORDER_NUMBER

I've used the below code. However this will only give the first entry made on "NOTES"
Is there a more efficient method of obtaining the most recent entry made under "NOTES" without having to change the ROWNUM

Reason is that an ORDER_NUMBER can have multiple ORDER_LINES and each line will have "NOTES" entered against it depending on the updates entered by the users. So when the report is executed the ORDER_NUMBER and corresponding ORDER_LINE should only show the most recently entered "NOTE_PAD"."NOTES"

Would really appreciate any assistance, as I have been trying to look for some answers online, but not been very lucky :cry:

SELECT *
FROM ( SELECT ORDER_DETAIL.CREATED_DATE,
ORDER_DETAIL.ORDER_NUMBER,
ORDER_DETAIL.ORDER_TYPE,
ORDER_AWB.ORDER_LINE,
ORDER_HEADER.RELATION_CODE,
ORDER_AWB.NOTES,
NOTE_PAD.NOTES_TEXT,
NOTE_PAD.CREATED_DATE AS NOTE_DATE,
ROW_NUMBER ( ) OVER ( PARTITION BY ORDER_DETAIL.ORDER_NUMBER,
ORDER_DETAIL.ORDER_LINE
ORDER BY ORDER_DETAIL.CREATED_DATE DESC ) AS SEQNUM
FROM ORDER_DETAIL,
ORDER_HEADER,
ORDER_AWB,
NOTE_PAD
WHERE ( "ORDER_DETAIL"."ORDER_NUMBER" = "ORDER_HEADER"."ORDER_NUMBER" )
AND ( "ORDER_HEADER"."ORDER_NUMBER" = "ORDER_AWB"."ORDER_NUMBER" )
AND ( "ORDER_AWB"."NOTES" = "NOTE_PAD"."NOTES" ( + ) ) )
WHERE ORDER_NUMBER = '991428'
AND ROWNUM <= 1

The result I need is as below, the "NOTES_TEXT" column needs to show the most recently entered data and not all the values entered. If there is no value entered then it should show blank as see here;


#2

Please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide

The order_awb table is "joined" to order_number, but not to a specific order line.
Also, you refer to rownum in the outer select, whereas you alias the field seqnum in the inner select.


#3

I think this is Oracle and not SQL Server. The ROWNUM refers to the literal ROWNUM reference that Oracle uses and the plus sign "+" is Oracle too.


#5

You need to join order_line from order_aws table to order_detail


#7

Try this:

select *
  from (select d.created_date
              ,d.order_number
              ,d.order_type
              ,d.order_line
              ,h.relation_code
              ,a.notes
              ,n.notes_text
              ,n.created_date as note_date
              ,row_number() over(partition by d.order_number
                                             ,d.order_line
                                 order by n.created_date desc
                                )
               as seqnum
          from order_header as h
               inner join order_detail as d
                       on d.order_number=h.order_number
               inner join order_awb as a
                       on a.order_number=d.order_number
                      and a.order_line=d.order_line
               left outer join note_pad as n
                       on n.notes=a.notes
         where h.order_number='991766'
 ) as t
 where seqnum<=1
;

If you still don't get notes, try changing "inner join" to "left outer join" on table order_awb


#8

Thank you very much, this works. However I've noticed that if I enter two NOTES_TEXT on the same date it will only show me one entry against the NOTES_TEXT. Even though the notes are an hour apart. I am guessing this is to do with the time/date format.

Is there a method I can covert in the NOTE_PAD.CREATED_DATE within the ROW_NUMBER ? so this way when it's sorts the result will be the most recent entry.

I've tried using below, but no luck:
ORDER BY TO_CHAR (NOTE_PAD.CREATED_DATE, 'DD-MM-YYYY HH:MM:SS')


#9

Not entirely sure, what you want, but try this:

select *
  from (select d.created_date
              ,d.order_number
              ,d.order_type
              ,d.order_line
              ,h.relation_code
              ,a.notes
              ,n.notes_text
              ,n.created_date as note_date
              ,row_number() over(partition by d.order_number
                                             ,d.order_line
                                 order by n.created_date desc
                                         ,n.notes_line desc
                                )
               as seqnum
          from order_header as h
               inner join order_detail as d
                       on d.order_number=h.order_number
               inner join order_awb as a
                       on a.order_number=d.order_number
                      and a.order_line=d.order_line
               left outer join note_pad as n
                       on n.notes=a.notes
         where h.order_number='991766'
 ) as t
 where seqnum<=1
;

#10

thanks. I did manage to resolve the issue, by converting date/time format.

ORDER BY TO_CHAR ( NOTE_PAD.CREATED_DATE, 'DD-MM-YYYY HH24:MI:SS' )