Solved

select distinct

Posted on 2013-11-19
5
286 Views
Last Modified: 2013-11-24
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?
0
Comment
Question by:AZZA-KHAMEES
  • 2
  • 2
5 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 333 total points
ID: 39659067
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 39659778
I think where status = 'approved' or status - 'initiated' needs to be added to the subquery.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 333 total points
ID: 39661055
good catch awking00! yes that is needed
0
 

Assisted Solution

by:AZZA-KHAMEES
AZZA-KHAMEES earned 0 total points
ID: 39661671
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
 

Author Closing Comment

by:AZZA-KHAMEES
ID: 39672430
thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now