SQL Duplicate rows in table

Hi Experts!


I am novice to SQL and for that reason I need some help to do do the following:

Table structure

[recnum],[field2],[field3],[field4]

I have import data from an "CSV" file into the table but ending up with duplicate rows.
The field [recnum] is obviously different but the condition to consider duplicate rows are:

field2, field3, field4 having same value.

Would you please help me to write the right code for this?

Thanks
Duke001
Duke001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Onn LightSAP Technical ConsultantCommented:
Hi Duke

Assuming that [recnum] does not have to be sequential, the below query will give you unique records for your table.

select min(recnum), field2, field3, field4
from yourtable
group by field2, field3, field4

Open in new window


You can use the above query to "Insert into" or copy/paste to a new table of your choosing.

Thanks and Regards
onn
0
Duke001Author Commented:
Thanks onn.
As I said, I'm "novice" to this subject and it would be helpful if you could right a complete code to create a new table based on this query.

Thanks
0
Paul_Harris_FusionCommented:
You can just insert     into <tablename> after the select clause

So

select min(recnum), field2, field3, field4
into yourNewTable
from yourtable
group by field2, field3, field4
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Paul_Harris_FusionCommented:
or more generally :

Select * INTO myNewTable
From
(
 select min(recnum) as recnum, field2, field3, field4
from yourtable
group by field2, field3, field4
) Q

The advantage of this approach is that it does not modify your select query so you can still select and execute it independently from the create table - useful for testing and development.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Duke001Author Commented:
There is a 'Q' after ')' which I don't understand! Is this a "typo"?
Also, I am assuming that I need to create the new table before, isn't it?
Thanks
0
Paul_Harris_FusionCommented:
The Q is not a typo.     This a technique known as a named subquery.    SQL Server requires that all sub-queries have a name -  in this case 'Q'.

With the above statement,  you do not need to create the table first.

Effectively you are creating a new table to store the results of your query.

If you DO create the table first with columns recnum, field2, field3, field4
then your Insert statement will be different:

Insert into myExistingTable (recname, field2, field3, field4)
 select min(recnum) as recnum, field2, field3, field4
from yourtable
group by field2, field3, field4
0
Onn LightSAP Technical ConsultantCommented:
Dear Duke

The Solutions / queries provide by  Paul_Harris_Fusion can be used as is.

Is my assumption that [recnum] does not necessarily have to be sequential correct?

Thanks and Regards
onn
0
Duke001Author Commented:
Thanks both for your patience and tech advise.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.