SQL table, finding values in rows and comparing.

Question

5.00/5 (1 vote)

See more:

Hi,
I have a table with columns: LastName, FirstName, DOB, CurrentWeight, CurrentEmpStat, DateOfVIsit, Patient_ID and Rec_ID.
In this table I can have same person with multiple entries. Record will have a unique Rec_ID.
I need to check the table and find the rows with same LastName, FirstName, DOB and Patient_ID.
Then I need to delete all multiple records (LastName, FirstName, DOB, Patient_ID)from that table except the newest record. What's the best way to achieve this?
Thankx in advance.
JL.

Hello again,
Actually, let me describe the problem a little better. What I actually want to achieve is to go through the table row by row and compare data in those rows. While comparing one to another and finding same values in four out of five columns in those rows it should delete the one whose 5th value is higher/lower (Edit: assuming OP knows which to choose) And continue processing until those four columns are unique in the table.

[Edit: Please dont leave CAPS ON :)]

HELLO AGAIN,
ACTUALLY, LET ME DESCRIBE IT LITTLE BETTER.
WHAT I'M LOOKING FOR LITTERALY IS TO GO THROUGH THE DATABASE ROW BY ROW AND COMPARE DATA IN THOSE ROWS. WHEN COMPERING ONE TO ONE AND FINDING SAME VALUES IN FOUR OUT OF FIVE COLUMNS IN THOSE ROWS IT SHOULD DELETE THE ONE WHICH VALUE OF THE FIFTH COLUMN IS HIGHER. OR SMALLER. THEN WHEN DELETE IS DONE IT SHOULD GO AND COMPARE IT TO THE ANOTHER ROW IN THE DATABASE TILL THE END. SO BASICALLY, NO ROWS WITH SAME FOUR VALUES.
HOPE, IT'S NOT THAT COMFUSING.. :)

JL

Posted 2-Jun-11 2:59am

JML_

Updated 2-Jun-11 4:38am

yesotaso

v4

Add a Solution

Comments

Wayne Gaylard 2-Jun-11 9:05am

Do you want to do this in c# or in SQL Management studio ?

Sunasara Imdadhusen 2-Jun-11 9:27am

you need to get only distinct row based on (LastName, FirstName, DOB, Patient_ID)?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dasblinkenlight · Accepted Answer · 2011-06-02T03:50:00

Assuming a table like this

SQL

create table test(
    rec_id uniqueidentifier -- your PK
,   rec_timestamp timestamp -- something to let us find the latest record
,   pid varchar(max) -- patient id, first name, last name, and DOB
,   first varchar(max)
,   last varchar(max)
,   dob date
)

this delete statement should do the trick:

SQL

delete lhs
from test lhs
where exists (
    select *
    from test rhs
    where lhs.first=rhs.first
      and lhs.last=rhs.last
      and lhs.dob=rhs.dob
      and lhs.pid=rhs.pid
      and rhs.rec_timestamp > lhs.rec_timestamp
)

Good luck!