Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/defaul...kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.
Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default...b;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

I have a table with a field called PID. This field can have duplicate
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

eliminating duplicate records from a table

Hi All

I am having problem in selecting the data from oracle. The problem is there are multiple enteries per customer, I want to select the latest updated value. I know the customer ids which has multiple entries corresssponding to them.

The columns are Cust_ID, Update_Date, Cust_Name,Cust_address.

Select ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

from Customer_Table s where s.cust_id in (101,102,103,104,105,106)

Group By ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

having .........(no idea)

I know this is not correct as I am still getting multiple records.

Any help will be useful.

Well, we don't really do oracle around these parts, but...

You should be able to say

count(*) > 1

in the having clause to get groups where there are > 1 rows in there.

|||

I guess that the later means the one with latest LAST_UPDATE. I do not know if this works for "oracle", but it does for SQL Server.

select *

from Customer_Table s

where s.cust_id in (101,102,103,104,105,106)

and last_update = (

select max(a.last_update)

from Customer_Table as a

where a.cust_id = s.cust_id

)

go

AMB

|||

Thanks for replying..I am sorry fro posting oracle query here..

I found the solution.....Thanks a lot...

Eliminating Duplicate Records

Hi

How can i eliminate duplicate records from a single table, when i use a query that links multipla tables together?

There is a surrogate key TABLE1 and i just can't seem to wrap my head around the layout.

TABLE1

ID | memQuestionaireID | QuestscoreID | DateOfAssessment | etc.

TABLE2
memQuestionaireID | MemberID | Answer1 | Answer2 | etc.

TABLE3

MemberID | Name | Surname | IDNo | etc.

t1.memQuestionaireID = t2.memQuestionaireID

t2.MemberID = t3.MemberID

That's how the tables link, obvious enough. How can i delete duplicate records from TABLE1 where MemberID occurs more than once and the t1.DateOfAssessment = MAX(DateOfAssessment)

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

I'm not very clued up on how to remove records or update records, Please help.

Kind Regards
Carel Greaves

See if this does what you need...

Code Snippet

DELETE t1

FROM Table1 t1

INNER JOIN Table2

on t1.memQuestionaireID = t2.memQuestionaireID

INNER JOIN

(

SELECT t2b.MemberID, max(t1b.DateOfAssessment) as DateOfAssessment

FROM table1 t1b

INNER JOIN table2 t2b

ON t1b.memQuestionaireID = t2b.memQuestionaireID

AND t2b.MemberID in

(

SELECT MemberID

FROM Table3

GROUP BY MemberID

HAVING count(*)>1

)

GROUP BY t2b.MemberID

) as list

ON t1.DateOfAssessment < list.DateOfAssessment

and t2.MemberID = list.MemberID

To view what will be delete w/o actually deleting, just change the first line from DELETE t1 to SELECT t1.*

|||

I'm a bit confused Carel,

Call me dense today, but it seems like the MemberID 'should' be in Table1 since it is the Member that has a memQuestionaireID, has a QuestScoreID and a DateOfAssessment. Then for Table2, it is the Questionaire that has a set of answers, etc. Please help me out here...

It seems like your Table1 has a one-to-many relationship with Table2. I'm assuming that a Member may have multiple instances of the same questionnaire (re-takes, etc.)

Is one questionnaire (MemQuestionnaireID) with a QuestScoreID and a DateOfAssessment directly linked to one or more set(s) of answers in Table2?

Is the QuestScoreID changed upon each re-take? (Shouldn't the score be directly related to a set of answers?)

I ask this because it seems from the design you presented that perhaps you want to also delete rows from Table2...

|||

I know the database structure is a bit stupid. Not my design!

I am only trying to remove duplicate entries in the QuestScore table.

Problem:

A member fills in a Questionaire - MemQuestionaires

The Querstionaire's Score is placed in the QuestScore Table.

There are duplicate entries in the QuestScore table with regards to a questionaireID from the memQuestionaires Table

If a duplicate exists, then the members can't log into there profiles anymore, so i need to remove the duplicates so that the members can access their profiles again.

I'm quite new to all of this, so any help would be greatly appreciated

Kind Regards

Carel Greaves.

|||

You gave us Table1, Table2, Table3...I'm guessing Table1 is QuestScore?

Did my code above work? If not, what was not quite right?

Are there multiple entries just in Table1, or is it Table3?

Please clarify and maybe post some sample data...

|||

Okay, so i'm a bito f a moron :-) he he

Here is the code i used to find the duplicates

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*

FROM questscore q, memQuestionaires m, Members me

WHERE q.memquestionaireID = m.memquestionaireID

AND m.MemberiD = me.memberid

AND me.ClientID = 124

ANd m.questionaireid = 85

AND q.sectionid >= 1000000

ORDER BY me.memberID

Here is my results:

MemberID memQuestionaireID QuestScoreID AnswerID AnswerValue DateCompleted 10005772 7615 273308 0 -500 50:59.7 10005789 11124 267771 0 -500 25:16.0 10005798 11129 259150 0 -500 44:41.1 10005798 11129 261032 0 -500 01:21.3 10005807 8214 237464 0 -500 59:18.1 10005819 11142 293317 0 -500 43:09.4

i have marked the duplicate values in red that i want to eliminate.

I need to eliminate only the record with the oldest date, and keep the newer one.

Hopefully when i've gone for my SQL course then i will only be half a moron :-) he he

Thanks for helping me out guys.

Kind Regards

Carel Greaves

|||

Wow, talk about a 'bolixed' up database...

I'm still confused, in your query above, you have a WHERE clause that indicates the results should be filtered down to QuestionaireID = 85, but your duplicate records are for memQuestionaireID 11129. What is the difference between QuestionaireID and memQuestionaireID? How did that happen?

Also, the DateCompleted is obviously not a date, but a number of hours. Is that the number of hours since some event? How are the hours determined?

Which row is the 'newer one'?

It would be very helpful, and keep us from wasting our time, if you would post the DDL for each of the tables, as well as some sample data for each table (in the form of INSERT statements.)

As you seen, you have folks sincerely attempting to help you, and to this point wasting our time because we just don't have the 'full' picture. You've inherited a very 'odd' database, and you need to help us so we can better help you.

|||

Here is sample data in the Tables.

Member Table

med_aid MemberID ClientID Name Surname Username Password NULL 10000000 1 NiftyName NiftySurname his test NULL 10000001 5 NiftyTest2 Surname2 nifty@.ybo.oa JBCYWO NULL 10000002 5 KeithTest WilsonTest willi willi

memQuestionaires Table

memQuestionaireID MemberID QuestionaireID LastPage Paused Complete MaxPages Sent LastSaved InitMail ReceiveMail UpdateDate 871 10000000 85 1 0 0 1 1 0 1 1 2007/02/2506:23 872 10000001 85 1 0 0 1 1 0 1 1 2007/02/2506:23 873 10000002 85 1 0 0 1 1 0 1 1 2007/02/2506:23

QuestScore Table

QuestScoreID memQuestionaireID AnswerID AnswerValue SectionID Page QuestType AnswerText AnswerShort Updatedate DefaultValue DateCompleted 4641 871 0 -9 361 1 9 NULL NULL

2007/02/0722:31

4642 872 0 -5 362 1 6 NULL NULL

2007/02/0722:31

4643 873 0 0 345 1 2 NULL NULL

2007/02/0722:31

When i executed this statement, i only used these three tables.

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*
FROM questscore q, memQuestionaires m, Members me
WHERE q.memquestionaireID = m.memquestionaireID
AND m.MemberiD = me.memberid
AND me.ClientID = 124
ANd m.questionaireid = 85
AND q.sectionid >= 1000000
ORDER BY me.memberID

I am only trying to remove the duplicate records for where ClientID = 124

AND QuestionaireID = 85

as for the 1000000, there it is just to narrow down the search for myself, there are a lot of duplicates.

There are a lot of members -> Members Table

Each member can fill in a number of questionaires -> QuestionaireID on memQuestionaires Table in this case 85

And all the questionaire's data is stored in the QuestScore Table which is linked to the memQuestionaires table using the memQuestionairesID.

There seems to be duplicate values in the QuestScore Table referring to the members.

The date completed is an actual DATETIME field in the database, i used excel to transfer the data to here. The newer date will be a actual date with the time in the database i.e. 2007/05/23 02:05:11

All the fields get inserted into the database via a application which i haven't seen.

I'm new to the company and am trying to figure a lot of it out for myself too.

If there is any more information you need, please don't hesitate to ask.

|||try this one,

DECLARE @.QuestScore TABLE (
QuestScoreID int
, memQuestionaireID int
, DateCompleted smalldatetime
)

DECLARE @.MemQuestionaire TABLE (
MemQuestionaireID int
, MemberID int
, QuestionaireID int
, UpdateDate smalldatetime
)

INSERT
INTO @.QuestScore
SELECT 1, 1, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 2, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 3, dateadd(day,-3, getdate())

INSERT
INTO @.MemQuestionaire
SELECT 1, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 2, 5, dateadd(day,-3, getdate())

select *
from @.memquestionaire

select *
from @.questscore

declare @.tobedeleted table( -- create a table var for the data to be deleted
memquestionaireid int
, datecompleted smalldatetime
)

insert
into @.tobedeleted -- the memquestionaireid and datecompleted to be deleted
select MIN(qq.memquestionaireid) as memquestionaireid -- just in case if they have the same date, get the lowest id
, mq.datecompleted
from @.questscore qq inner join
(
select m.memberid
, m.questionaireid
, MIN(q.datecompleted) as datecompleted -- delete the lowest date
from @.memquestionaire m inner join
@.questscore q on m.memquestionaireid = q.memquestionaireid
group by
m.memberid
, m.questionaireid
having count(m.memberid) > 1 -- member having more than 1 quest score
) mq on qq.datecompleted = mq.datecompleted
group by
mq.datecompleted

delete @.questscore -- delete in questscore
from @.questscore q inner join
@.tobedeleted dq on q.memquestionaireid = dq.memquestionaireid
and q.datecompleted = dq.datecompleted

delete @.memquestionaire -- delete in memquestionaire
from @.memquestionaire m inner join
@.tobedeleted dq on m.memquestionaireid = dq.memquestionaireid

select *
from @.questscore

select *
from @.memquestionaire|||

Thanks, that's awesome, that's why i come to you guys for help.

Another quick question, that i think it might be easier to do, i just can't do it. (I just thought of it now)

If i added memQuestionaireID and SectionID together to create a unique field, and then filter out the duplicates by taking out the old dates within the duplicates it might be easier, but how would i go about accomplishing this?

I don't know how to compare the dates against itself to get keep the new date and get rid of the old date?

sorry for all of this i'm just trying to learn from all the things myself?

|||

Thanks, that was a start.

Here is an example of the 'best' form for DDL and sample data. In this fashion, each person that wants to try and help you can just run this code and have 'your' problem to work with. Without this, folks are turned away because it takes so much time and effort to duplicate your situation -and helps to keep us from running off on tangents that don't really help you.

Now if you would only add to the sample data so that examples of the duplicate records you want to find and delete are represented, we 'should' be able to help you.

Code Snippet

DECLARE @.Members table
( med_aid int,
MemberID int,
ClientID int,
Name varchar(20),
Surname varchar(20),
Username varchar(100),
Password varchar(20)
)

INSERT INTO @.Members VALUES ( NULL, 10000000, 1, 'NiftyName', 'NiftySurname', 'his', 'test' )
INSERT INTO @.Members VALUES ( NULL, 10000001, 5, 'NiftyTest2', 'Surname2', 'nifty@.ybo.oa', 'JBCYWO' )
INSERT INTO @.Members VALUES ( NULL, 10000002, 5, 'KeithTest', 'WilsonTest', 'willi', 'willi' )

DECLARE @.memQuestionaires table
( memQuestionaireID int,
MemberID int,
QuestionaireID int,
LastPage int,
Paused int,
Complete int,
MaxPages int,
Sent int,
LastSaved int,
InitMail int,
ReceiveMail int,
UpdateDate datetime
)

INSERT INTO @.memQuestionaires VALUES ( 871, 10000000, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 872, 10000001, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 873, 10000002, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )

DECLARE @.QuestScore table
( QuestScoreID int,
memQuestionaireID int,
AnswerID int,
AnswerValue int,
SectionID int,
Page int,
QuestType int,
AnswerText int,
AnswerShort int,
Updatedate int,
DefaultValue int,
DateCompleted datetime
)

INSERT INTO @.QuestScore VALUES ( 4641, 871, 0, -9, 361, 1, 9, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4642, 872, 0, -5, 362, 1, 6, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4643, 873, 0, 0, 345, 1, 2, '', '', NULL, NULL, '2007/02/07 22:31' )

|||very clean example arnie.. i wonder how your desk looks like |||

Sorry i'm sending you guys on a wild goose chase like this, and thanks a lot Arnie, i'd buy you a case of beer if i knew where you were.

I'm really learning a lot from this!

I just thought that it might be easier, if i had to add the memQuestionaireID and SectionID together in the QuestScore table to get a single unique value and remove the duplicates based on the newer unique value according to the Date, i.e. Only removing the old dates.

Sorry about this, i only saw it now.

That way i'm only using a single table.

|||

Thanks,

And my desk is under the stuff somewhere, or so it was a few months ago...

And UPS serves Portland, OR! But it would be much more enjoyable to share, so if you can wrangle it, start lobbying management to let you come to the PASS (Professional Association for SQL Server) Conference in September. (http://sqlpass.org/)

|||

Cummon guys, i'm a newbie at SQL Server in South Africa and it's late at night and my CEO's say i can't go home until i get this problem sorted out.

Please could you guys help me out quick?

eliminate duplicate results

Hi,

Here is a brief description of a problem I'm currently experiencing:

We have a phone line that employees can call to report their status. There is no limit on how often they can call. Each time a call is completed a record is inserted into a table with the following info: the employees unique id, theire current status, and the location of an audio file should they have chosen to leave a message. When I query this table I'd like to only return the most recent call for each employee, disregarding all the rest.

So...

When I do a simple query like:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId

I get only one record, the most recent one, for each employee who has called. However as soon as I try to get another field I get one record for each different value in that field.

a second query:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId,
r.ircStatus
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId, r.ircStatus

This returns one record for each status value that each employee has.

I guess my question is how do I retrieve all of the fields in a record with the most recent date for each unique employee.

Thanks for the help.Read this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31147

And change MIN to MAX|||Thanks for the quick reply.

Which post in that thread am I looking at?|||Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect

Thanks Brett!

Eliminate Duplicate Records

Hi

How can i eliminate duplicate records from a single table?

There is a surrogate key in the table and i just can't seem to wrap my head around the layout.

ID | MemberID | Name | Surname | DateOfAssessment | etc.

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

Kind Regards
Carel Greaves

This should give you an idea of one method to accomplish your goal.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int IDENTITY,
MemberID int,
[Name] varchar(20),
SurName varchar(20),
DateOfAssessment datetime
)

INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/25' )
INSERT INTO @.MyTable VALUES ( 3, 'Mary', 'Smith', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 4, 'Susy', 'Williams', '2007/05/22' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/04/26' )

SELECT *
FROM @.MyTable

DELETE @.MyTable
FROM @.MyTable m
JOIN (SELECT
MemberID,
DateOfAssessment = max( DateOfAssessment )
FROM @.MyTable
GROUP BY MemberID ) dt
ON ( m.MemberID = dt.MemberID
AND m.DateOfAssessment <> dt.DateOfAssessment
)

SELECT *
FROM @.MyTable

ID MemberID Name SurName DateOfAssessment
--
2 3 Mary Smith 2007-05-26 00:00:00.000
3 4 Susy Williams 2007-05-22 00:00:00.000
4 2 Bill Jones 2007-05-26 00:00:00.000

|||
Thanks you very much.

Eliminate duplicate records

department table got duplicate data......

when i insert data from department to employee table, i want to eliminate duplicate and insert unique data.

department

emp_id emp_name emp_address

10 mary melville,ny

10 mary longisland,ny

11 linsy sugarland,tx

12 sam fairfax,va

12 sam dice,va

i want result in employee table....How can i get it with fastest query...i got tons of record to insert.

emp_id emp_name emp_address

10 mary melville,ny

11 linsy sugarland,tx

12 sam fairfax,va

insert into employee(emp_id,emp_name,emp_address)
select emp_id, max(emp_name), max(emp_address)
from department

where emp_id is not null

If you are using SQL Server 2005, give a look to the ROW_NUMBER() functon in books online. One technique is to create a "sequence number" for each grouping using the ROW_NUMBER() function and to select the records that have a generated "sequence number" of 1.

Also, something like this can be done:

Code Snippet

declare @.mockup table
( emp_id integer,
emp_name varchar(10),
emp_address varchar(25)
)
insert into @.mockup
select 10, 'mary', 'melville,ny' union all
select 10, 'mary', 'longisland,ny' union all
select 11, 'linsy', 'sugarland,tx' union all
select 12, 'sam', 'fairfax,va' union all
select 12, 'sam', 'dice,va'
--select * from @.mockup

select emp_id,
emp_name,
( select max(emp_address)
from @.mockup b
where a.emp_id = b.emp_id
) as emp_address
from @.mockup a
group by emp_id, emp_name

/*
emp_id emp_name emp_address
-- - -
10 mary melville,ny
11 linsy sugarland,tx
12 sam fairfax,va
*/

|||

For SS2005:

Code Snippet

create table #dept( emp_id int, emp_name varchar(50), emp_address varchar(50) )

insert into #dept

select 10, 'mary', 'melville,ny'

union all select 10, 'mary', 'longisland,ny'

union all select 11, 'linsy', 'sugarland,tx'

union all select 12, 'sam', 'fairfax,va'

union all select 12, 'sam', 'dice,va'

select emp_id, emp_name, emp_address

from

(

select *, row_number() over (partition by emp_id order by emp_id, emp_name desc, emp_address desc) as rn

from #dept

) emp

where rn = 1

For SS2000:

Code Snippet

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

|||

The trick here is to get a list of the records you want to keep. However, using MAX on character fields isn't the way to go (unless you don't care which record to keep). Hopefully you've got a column which gives some sort of order to the data (eg datecreated) so you can choose the most recent one, otherwise its a guessing game as to which one is "keepable"

So assuming you have one... ;-)

INSERT INTO employee (emp_id, name, emp_address)

SELECT d.emp_id, d.name, d.emp_address
FROM department d

INNER JOIN

(SELECT emp_id, MAX(datecreated) AS dt

FROM department

GROUP BY emp_id) AS dist

ON d.emp_id = dist.emp_id and d.datecreated = dist.dt

HTH!

|||

when i use max function....i get this error....

i get an error like this.....
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

|||When you try the insert? Check that your column definitions in the department and employee tables match. You may have a smaller field in the destination table...|||

The problem isn't with max(), it's a mismatch between column definitions in your source and your target.

|||

Getting this error...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'datecreated'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'datecreated'.

|||

I guess you don't have a column matching that in your table ;-)

My example was based upon the presumption that you had a field such as that in your table. You won't be able to just copy and paste it and get results. It was based upon the following DDL

CREATE TABLE departments
(emp_id int, emp_name varchar(100), emp_address varchar(100), datecreated datetime)

Basically, if you do have a field (in my example datecreated) that tells you which record is the one you want to keep then you can use a variation on my example. If you don't have one and you're happy for the choice to be made based arbitarilly then go with Dales option. His can be used straight out of the box.

Sorry for any confusion.

|||

Is there any internal field of table record i can use?.

like in oracle, do we have system variable/ records created along with insert of data in a row in sql server?.

so can i use that date created field?.

|||

I'm afraid not. If you don't have an explicit date field you can't use my example.

If the ddl of your table is the same as in Dales example, you should go with one of his excellent suggestions.

|||

i used dale solution...max thing.

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

works fine today.

Eliminate Duplicate ID's in this StoredProcedure

I have a stored procedure that I use for Monthly Billing

delete from BillingCurrent

insert into BillingCurrent([Name],Address,City,State,Zip,InvoiceID,CustomerID,[Date],InvoiceTotal)

SELECT Customers.Name,Customers.Address,Customers.City,Customers.State,Customers.Zip,Invoices.InvoiceID,Customers.CustomerID,Invoices.Date,Invoices.InvoiceTotal

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)and CONVERT(varchar(15), GETDATE(), 112)

This works great, but if a customer has more than one invoice open it adds that Customer again (for each invoice that is not 0.00. How can I change this SP to only add each Customer once regardless of how many invoices they have?

In your query you tried to work with Invoice.Date and Invoice.InvoiceTotal fields.

If you have following data in your Invoices table for CustomerId=1:

Date InvoiceTotal

1.1.2006 1000

1.1.2007 2000

What do you want to save into BillingCurrent?

Which SQL Server version do you use?

|||

Cammyr:

You state that you want to reduce the number of rows to 1 whenever a customer has more than one invoice during a month. However, what I don't understand is what we are supposed to put in these three fields if we are returning only one row: (1) InvoiceID, (2) Date, and (3) InvoiceTotal. I have therefore put together two methods to return only one row per customer. The first method simply returns this information for the last invoice posted for a given customer. There is a good chance that this is not what you want but would rather have the total of all invoices summed into the InvoiceTotal column:

--
-- This method uses uses (1) CROSS APPLY and (2) ROW_NUMBER() to
-- find the "last" invoice posted to a particular customers account to
-- display that information with the customer information.
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
i.InvoiceID,
c.CustomerID,
i.Date,
i.InvoiceTotal
FROM Customers c
cross apply
( select invoiceId,
row_number ()
over ( order by Date desc,
invoiceId desc
)
as seq,
Date,
InvoiceTotal
from Invoices p
where p.customerId = c.customerId
and CONVERT(varchar(15), p.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
) i
where i.seq = 1

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 14.92

--
-- This method instead posts the SUM of all invoices into the InvoiceTotal
-- column and chooses the highest InvoiceID for the InvoiceID column and
-- the highest Date for the Date column. It is not clear what is requried
-- for these fields
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
max (i.InvoiceID) as InvoiceID,
c.CustomerID,
max (i.Date) as Date,
sum (i.InvoiceTotal) as InvoiceTotal
FROM Invoices i
INNER JOIN Customers c
ON i.CustomerID = c.CustomerID
WHERE CONVERT(varchar(15), i.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
group by c.CustomerId,
c.Name,
c.Address,
c.City,
c.State,
c.Zip

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 94.76

( I completely agree with Konstantin's questions. )

|||

I want to save Name,Address,City,State,Zip,InvoiceID,CustomerID in BillingCurrent

But really I guess I don't need InvoiceID or InvoiceTotal as I have a relationship between BillingCurrent and InvoiceDetails On CustomerID Column that will get all the InvoiceDetails for each CustomerID.

I'm using vb.express with sql.express

|||

You are both right there were quite a few fields in my insert statement that were not needed. All I really needed was CustomerID,Name,Address,City and Zip because I can use the getchildrows method to get all of the corresponding invoicedetails for each customerID.

This is what I came up with, it seems to be working fine.

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

I'm sure yours works for that situation Kent, so I'm marking yours as the answer.

Thanks

Eliminate duplicate data

Hi,
I want to eliminate duplicate entries from different tables into a single
new table and keep the reference from the original data.
Example:
Table1: toto, titi
Table2: toto, titi, tutu
Table3: tutu, tata
the new table will contain:
NewTable: toto, titi, tutu, tata
and i want to keep a reference that allows to retrieve that toto comes from
Table1 and Table2
tutu comes from Table2 and Table3...
if someone can help me it would be great.
Thanks.
Beter than a single new table, You can make a View
UNION will eliminate duplicate data.
SELECT Field FROM Table1
UNION
SELECT Field FROM Table2
UNION
SELECT Field FROM Table3
To know where 'toto' come from:
SELECT TableName FROM
(
SELECT 'Table1' AS TableName, Field FROM Table1
UNION
SELECT 'Table2' AS TableName, Field FROM Table2
UNION
SELECT 'Table3' AS TableName, Field FROM Table3
) Tmp
WHERE Field='toto'
Laurent.
<Christophe> wrote in message news:OVqTbQGLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to eliminate duplicate entries from different tables into a single
> new table and keep the reference from the original data.
> Example:
> Table1: toto, titi
> Table2: toto, titi, tutu
> Table3: tutu, tata
> the new table will contain:
> NewTable: toto, titi, tutu, tata
> and i want to keep a reference that allows to retrieve that toto comes
from
> Table1 and Table2
> tutu comes from Table2 and Table3...
> if someone can help me it would be great.
> Thanks.
>

Eliminate duplicate data

Hi,
I want to eliminate duplicate entries from different tables into a single
new table and keep the reference from the original data.
Example:
Table1: toto, titi
Table2: toto, titi, tutu
Table3: tutu, tata
the new table will contain:
NewTable: toto, titi, tutu, tata
and i want to keep a reference that allows to retrieve that toto comes from
Table1 and Table2
tutu comes from Table2 and Table3...
if someone can help me it would be great.
Thanks.Beter than a single new table, You can make a View
UNION will eliminate duplicate data.
SELECT Field FROM Table1
UNION
SELECT Field FROM Table2
UNION
SELECT Field FROM Table3
To know where 'toto' come from:
SELECT TableName FROM
(
SELECT 'Table1' AS TableName, Field FROM Table1
UNION
SELECT 'Table2' AS TableName, Field FROM Table2
UNION
SELECT 'Table3' AS TableName, Field FROM Table3
) Tmp
WHERE Field='toto'
Laurent.
<Christophe> wrote in message news:OVqTbQGLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to eliminate duplicate entries from different tables into a single
> new table and keep the reference from the original data.
> Example:
> Table1: toto, titi
> Table2: toto, titi, tutu
> Table3: tutu, tata
> the new table will contain:
> NewTable: toto, titi, tutu, tata
> and i want to keep a reference that allows to retrieve that toto comes
from
> Table1 and Table2
> tutu comes from Table2 and Table3...
> if someone can help me it would be great.
> Thanks.
>

Eliminate duplicate data

Hi,
I want to eliminate duplicate entries from different tables into a single
new table and keep the reference from the original data.
Example:
Table1: toto, titi
Table2: toto, titi, tutu
Table3: tutu, tata
the new table will contain:
NewTable: toto, titi, tutu, tata
and i want to keep a reference that allows to retrieve that toto comes from
Table1 and Table2
tutu comes from Table2 and Table3...
if someone can help me it would be great.
Thanks.Beter than a single new table, You can make a View
UNION will eliminate duplicate data.
SELECT Field FROM Table1
UNION
SELECT Field FROM Table2
UNION
SELECT Field FROM Table3
To know where 'toto' come from:
SELECT TableName FROM
(
SELECT 'Table1' AS TableName, Field FROM Table1
UNION
SELECT 'Table2' AS TableName, Field FROM Table2
UNION
SELECT 'Table3' AS TableName, Field FROM Table3
) Tmp
WHERE Field='toto'
Laurent.
<Christophe> wrote in message news:OVqTbQGLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to eliminate duplicate entries from different tables into a single
> new table and keep the reference from the original data.
> Example:
> Table1: toto, titi
> Table2: toto, titi, tutu
> Table3: tutu, tata
> the new table will contain:
> NewTable: toto, titi, tutu, tata
> and i want to keep a reference that allows to retrieve that toto comes
from
> Table1 and Table2
> tutu comes from Table2 and Table3...
> if someone can help me it would be great.
> Thanks.
>