[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


SQL Query - Filtering

Posted on 2011-03-17
Medium Priority
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

656 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