Duke001
asked on
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
I am novice to SQL and for that reason I need some help to do do the following:
Table structure
[recnum],[field2],[field3]
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
So
select min(recnum), field2, field3, field4
into yourNewTable
from yourtable
group by field2, field3, field4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Also, I am assuming that I need to create the new table before, isn't it?
Thanks
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
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
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
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
ASKER
Thanks both for your patience and tech advise.
ASKER
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