stack stack - 3 months ago 5
SQL Question

Get Max Entry_Sr_no from another table in SP

I have a SP, which gives me

mkey
and
doc_no
from one table.

Now I want to get the max of
entry_sr_no
of that respective
mkey
from the another table.

Here is my SP

alter procedure set_auto_action
as
begin

select mkey, doc_no from inward_doc_tracking_hdr
where status_flag not in (5,13)
end


I want to get
max
from
inward_doc_tracking_trl
table

The relation of both the table is something like below

inward_doc_tracking_hdr
table
mkey
is equal to
inward_doc_tracking_trl
table
ref_mkey


kindly suggest how to do this in SP

Answer

A simple join with MAX() should do it :

select hdr.mkey, hdr.doc_no,MAX(trl.entry_sr_no) as max_no
from inward_doc_tracking_hdr  hdr
JOIN inward_doc_tracking_trl trl
 ON(hdr.mkey = trl.ref_mkey)
where hdr.status_flag not in (5,13) 
GROUP BY hdr.mkey, hdr.doc_no
Comments