select distinct

Hi Experts
i am seeking help in writing the logic for an sql query

i have 2 tables with one to many relationship,

table1
-------------
reference
-------------
111
222
333
------------

table2
---------------------------------------------------------
id     reference    status         desc        
---------------------------------------------------------
1     111              initiated      initiated  
2     111              approved    First          
3     111              approved    Second      
4     111              assigned     assigned  

5     222              initiated      initiated    
6     222              approved     First        

7     333            initiated        initiated
8     333            approved      First
9     333            approved      Second
------------------------------------------------

now i need to get all references from the first table and match it with only last record from second table
ex.
Reference 333 with record id 9 from table 2
Reference 222 with record id 6 from table 2 and so on

based on condition
where status = approved or initiated

any suggestion?
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
good catch awking00! yes that is needed
0
 
PortletPaulConnect With a Mentor freelancerCommented:
use row_number() to determine "last record"

, row_number() over (partition by reference order by id DESC) as rn

SELECT *
FROM table1
INNER JOIN (
            SELECT *
                , row_number() OVER (
                    PARTITION BY reference ORDER BY id DESC
                    ) AS rn
            FROM table2
            ) AS t2 ON table1.reference = t2.reference
                   AND t2.rn = 1

Open in new window

0
 
awking00Connect With a Mentor Commented:
I think where status = 'approved' or status - 'initiated' needs to be added to the subquery.
0
 
AZZA-KHAMEESConnect With a Mentor Author Commented:
thank you for the replies, also i manged to solve my issues using this query

select a.Reference, a.Status from table2 as a inner join table1 as b 
on (a.Reference = b.Reference and (b.Status = 'initiated' or b.Status='approved'))
where a.id in (select Max(id) from table2 as b group by Reference) 
and (a.Status = 'initiated' or a.status = 'approved')

Open in new window


and its working perfectly

thanks
0
 
AZZA-KHAMEESAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.