Solved

SQL Query - Filtering

Posted on 2011-03-17
3
329 Views
Last Modified: 2012-05-11
Hello experts,

I have a list with a mix of duplicate and non-duplicate records.  I need to filter this list so that I pull the correct duplicate since some data in the duplicate determine if it's the correct record I want as well as the none duplicate.  

The first example have duplicate ID for some records.  With these duplicate, there's a reference ID.  If the reference ID is not null, then that's the record I want and omit/exclude the one with the null reference.  However, there are some record that doesn't duplicate but have null reference.  I still want to pull those since the reference hasn't been added to it yet.

Example:
ID      DESCRIPTION      REFERENCE ID
5627      3"x4" MWY      NULL
5627      3"X4" MWY 304      5627
7678      7" AGT COL      NULL
7688      12" AGT COL      NULL
7688      12" AGT COV      2989
7899      MOT MTG BLT 4"x3"      NULL
7899      MOT MTG BLT 4"X3" THD      7899


The result below is what it should look like after the filter.  I tried to filter it with a number of ways in trying to get all the records that I want with excluding the duplicate record that have the null reference, but no luck.

Result:
ID      DESCRIPTION      REFERENCE ID
5627      3"X4" MWY 304      5627
7678      7" AGT COL      NULL
7688      12" AGT COV      2989
7899      MOT MTG BLT 4"X3" THD      7899

Is there a way I can query it so that if it's a duplicate ID with a null reference to not pull that record, but if it's not duplicate to pull that record regardless if reference ID is null or not?  Would 2 query be require and use a union to join them?
0
Comment
Question by:holemania
3 Comments
 
LVL 19

Expert Comment

by:Bardobrave
ID: 35156286
Try something like this:

SELECT ID, MAX(DESCRIPTION), MAX(REFERENCE ID) FROM TABLE
GROUP BY ID HAVING REFERENCE ID IS NOT NULL

I'm not very sure if the clause in HAVING should be "IS NOT NULL" or only "NOT NULL" try both if you get an error with the first one.
0
 
LVL 25

Accepted Solution

by:
slam69 earned 500 total points
ID: 35156362
select *
from (select Id, description,reference_id, row_number() over (partition by id order by referenceid desc) as rn
from yourtable) as p
where rn = 1

it partitions your data by the ids and assigns it a row number, cant test so not sure if teh order by in the nested select should be ascending or desc so you may need to switch.

it will only work if the reference id field is only populated for teh correct line you want to select

run the nested select

select Id, description,reference_id, row_number() over (partition by id order by referenceid desc) as rn
from yourtable

to make sure the corr3ect rows are tagged with a 1 , if not remove the desc and re run and your good to go
0
 

Author Closing Comment

by:holemania
ID: 35156474
Thank you.  That worked wonderfully.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

23 Experts available now in Live!

Get 1:1 Help Now