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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
1 FROM DUAL wont work with additional columns ?? 4 36
sql help 8 55
T-SQL Query to include null values 3 29
Need some help to cast ntext to nvarchar SQL 2000 7 31
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

805 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