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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 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