SQL Query - Filtering

Posted on 2011-03-17
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.

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.

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?
Question by:holemania
LVL 19

Expert Comment

ID: 35156286
Try something like this:


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.
LVL 25

Accepted Solution

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

Author Closing Comment

ID: 35156474
Thank you.  That worked wonderfully.

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

760 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

19 Experts available now in Live!

Get 1:1 Help Now