Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Exclude Duplicate Records


Hello

I'm developing my fist Integration Service and I have this operations:

Reading from a XML
Check for duplicate records and discard them
Insert the result into the database

The XML I don't control and could came with duplicate records that I have to discard. How can I find them?

I want to find the duplicates in the XML and not in database.

 

tkx for the help
Paulo Aboim Pinto
Odivelas - Portugal


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Returning Records That Exclude Top 10% Of Values
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?

2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159

View Replies !   View Related
How To Exclude Records That Have A Common Column
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:

SELECT member_id, service_id
FROM pat_prg_info ppi

This query produces results like the following:

member_id    service_id
1001             1
1001             2
1003             9
1003             9
1004            2
1004            9

I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?

View Replies !   View Related
Tables Joined On Multiple Columns, Exclude Records Found In Table A
I'm using SQL server 200 

Table A has columns CompressedProduct, Tool, Operation

Table B in a differnt database has columns ID, Product, Tool Operation

I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.

But I want to be able to select any records that are in table A but not in Table B.

ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.

This code return all the records from A. I need to filter out the records found in Table B.

---------------------------------------------------------------------------------------------------------------------------------

SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
  JOIN tblProduct p ON ts.ProductID = p.ProductID
  JOIN tblTool t ON ts.ToolID = t.ToolID
  JOIN tblOperation o ON ts.OperationID = o.OperationID
 WHERE ts.ToolID=66
 ') oq
 LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation

View Replies !   View Related
Duplicate Records
Hi

Can anyone tell me how to stop a SQL query displaying duplicate records within a table

Thanks Alot

View Replies !   View Related
Duplicate Records
Can someone tell me the best procedure when trying to find duplicate records within a table(s)?

I'm new using SQL server and I have been informed that there maybe some DUPS within unknown tables. I need to find these DUPS.

If someone can tell me how to perform this procedure I would apprciate it. And if you reply can also include examples that i could follow for my records.

Thanks for the help?

-SQL Rookie

View Replies !   View Related
Duplicate Records
Is there a code sample I can get for Deleting duplicate records from a sql table.

View Replies !   View Related
Duplicate Records
Is there a way to find duplicate records in a table using code. We have about 500,000 records in this table.
Thanks.

View Replies !   View Related
Duplicate Records
Yes, I know this subject has been exhausted, but I need help in locating the discussion which took place a few months ago.
Sharon relayed to the group a piece of software (expensive) which would help in my particular situation. I grabbed a demo and have gotten the approval for purchase. Unfortunately, I don't have the thread with me at work.

The problem:

Number Fname Lname Age ID
123 John Franklin 43 1
123 Jane Franklin 40 2
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 29 6
126 Fred Johnson 39 7
127 Gene Simmons 47 8

The idea would be to get only unique records from the Number column. I don't care about which information I grab from the other columns, but I must have those fields included.
If my resultant result set looked as follows, that would be fine. Or any other way, as long as all of the fields had information and there were only unique values in the Number field.

Number Fname Lname Age ID
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 39 7
127 Gene Simmons 47 8

If anyone remembers this discussion, mainly the date, I would really appreciate it.

Thanks

Gregory Taylor
MIS Director
Timeshares By Owner

View Replies !   View Related
Duplicate Records
Hi All,

How to check for the duplicate records in the table? Thanks.

View Replies !   View Related
Duplicate Records
Hi,

I have a field called user_no

i want to find out which ones are duplicates in the user_no field

the data in user_no is like this

111-222-345-666

so there are 10,000 records in the table and i want to find out the duplicate records in them

can someone tell me how my query will be

todd

View Replies !   View Related
Duplicate Records
I have two tables, one contains all work orders, the second contains records on work orders that are linked to customoer orders. I'm trying to create a query that will return specific fields from the table that contains orders in the linked order table, and only the work orders in the all order table that (work_order) do not exist in the linked order table (demand_supply_link). I have tried several queries and cannot get the results I desire. Here is the query I am currently trying.

SELECT DISTINCT WORK_ORDER.DESIRED_WANT_DATE as 'Want Date', DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID as 'WO Id',
WORK_ORDER.DESIRED_QTY as 'End Qty', DEMAND_SUPPLY_LINK.SUPPLY_PART_ID as 'Part Id', CUST_ORDER_LINE.CUSTOMER_PART_ID as 'Cust Part',
OPERATION.RESOURCE_ID as Resource, PART.DESCRIPTION as Description, CUSTOMER.NAME as Name
FROM ((((DEMAND_SUPPLY_LINK INNER JOIN CUST_ORDER_LINE ON DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID)
INNER JOIN WORK_ORDER ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = WORK_ORDER.BASE_ID)
INNER JOIN OPERATION ON WORK_ORDER.BASE_ID = OPERATION.WORKORDER_BASE_ID) INNER JOIN PART ON WORK_ORDER.PART_ID = PART.ID)
INNER JOIN (CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID) ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
WHERE WORK_ORDER.DESIRED_WANT_DATE Is Not Null AND OPERATION.RESOURCE_ID in ('ASSY','FAB 1','PLAY TRK')
AND WORK_ORDER.STATUS='R'

UNION
SELECT distinct work_order.desired_want_date as 'Want Date', work_order.BASE_id as 'WO Id',
work_order.desired_qty as 'End Qty', work_order.part_id as 'Part Id', operation.resource_id as Resource,
part.description as Description
FROM WORK_ORDER INNER JOIN PART ON PART_ID=WORK_ORDER.PART_ID INNER JOIN OPERATION ON WORK_ORDER.BASE_ID=OPERATION.WORKORDER_BASE_ID
WHERE WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL AND OPERATION.RESOURCE_ID IN ('ASSY','FAB 1', 'PLAY TRK')
AND WORK_ORDER.STATUS='R'

This is the error I receive:
Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

The all orders table (work_order) will not have the other fields to link to as there is no customer order linked to them.

Can anyone help. Thanks!

View Replies !   View Related
Duplicate Records
Table1 has shop# and shop_id. Every shop# should have only one shop_ID. There has been a few data entry errors where a shop# has duplicate a shop_id. How to write a query for shop#s that have more than one shop_id?

View Replies !   View Related
Duplicate Records
anybody know what sql statement can be used to pull duplicate records from an sql table.

View Replies !   View Related
Duplicate Records
how to we check in for duplicate records without using sort (remove duplicateS)

i need to remove duplicates based on four columns.

please let me know

View Replies !   View Related
Duplicate Records
I have a table with phone numbers.

I want to find if any phone number are repeated more then once. How can I accomplish this?

View Replies !   View Related
Duplicate Records
Hi,

Not so sure how simple this question is but here is what happened.  I installed SQL Server 2005 on a new Win Server 2003.  I exported the tables and their data from the old machine to the newly established database on the new machine. 

It looks like all my records were duplicated.  When I try to delete one of the duplicates it won't work because both rows are effected.  I can't set my primary key now and if I try to create a new database with the primary key already set than the import fails.

Any one run into this before or know what's going on?

Any help ASAP would really be appreciated.

Thanks,

Alice

View Replies !   View Related
Duplicate Records In A Table
How do i remove duplicate records from a table with a single query without using cursors or anything like that.Sample :tempCol11221P.S The table has only one column  

View Replies !   View Related
Duplicate Records On Database
hi all,
How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks the submit button, i want to check through the database if the exact records exist in the database before the data is saved. if the user is registered on the database, he wont be allowed to login. how can i acheive this?
i thought of using the comparevalidator but i'm not sure how to proceed.
thanks

View Replies !   View Related
Records Duplicate When Edited...?
Hi,I have written a web application using dreamweaver MX, asp.net, and MSsql server 2005.The problem I am having occurs when I attempt to edit a record. I have setup a datagrid with freeform fields so that the user can click on edit, make the required changes within the data grid then click update. The data is then saved to the database. All this was created using dreameaver and most of the code was automatically generated for me.The problem is that, not everytime, but sometimes when I go to edit a record once I hit the update button to save the changes the record is duplicated 1 or more times. This doesnt happen everytime but when it does it duplicates the record between 1 and about 5 times. I have double checked everything but cannot find anything obvious that may be causing this issue. Does anyone have any suggestions as to what I should look for? Is this a coding error or something wrong with MSsql? Any ideas?Thanks in advance-Mitch 

View Replies !   View Related
Delete Duplicate Records
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates?

I need this done in SP.

View Replies !   View Related
Prevent Duplicate Records
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.

View Replies !   View Related
Return Duplicate Records
Hello experts,I'm trying the run the following query with specific intentions.I would like the query to return 5 results; i.e., 4 distinct and oneduplicate. I am only getting, however, 4 distinct records. I wouldlike the results from the '007' id to spit out twice.I'm not using 'distinct,' and I've tried 'all.' I realize that Icould put my 5 employee id's in a table and do a left or right join; Iwould like to avoid that, however. Any thoughts?SelectEmployee_last_name,Employee_first_name


Quote:

View Replies !   View Related
Deleting Duplicate Records
Hi All,
I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records.

It is something like this,

MyCol
7
7
7
7
7
7
7
7
7
7

Now i m trying to delete 10th record or any record then it gives me error
"Key column information is insufficient or incorrect. Too many rows were affected by update."

What should i do if i want only 4 records insted 10 records in my table?
How do i delete the 6 records from table?

Plz help me.


Regards,
Shailesh

View Replies !   View Related
Need To Remove All Duplicate Records.
Hi

I have a data in one table like below.


EDITION PRODUCT INSERTDATE
---------- ------------ ----------------------
CNE TN-Town News 12/19/2007 12:00:00 AM
TN TN-Town News 12/19/2007 12:00:00 AM

What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.

How to do that?. Can anybody help me?

Thanks
Venkat

View Replies !   View Related
Picking Out Duplicate Records
Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?

ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195- 11111111- MA1E2- Annual - 1- 2006- 34
1205- 11111111- MA1E2- Supplemental- 1- 2006- 40 (S)
1194- 11111111- MA1E1- Annual -1- 2006- 35
1204- 11111111- MA1E1- Supplemental- 1- 2006- 40 (S)

Here is the SQL I'm using to get all the records from the studentresults table, for first years only:

SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1

How do I change this to pick out only the supplemental exam results?

Regards,

sabatier

View Replies !   View Related
Deleting Duplicate Records.
I need a sql statement to delete duplicate records.

I have a college table with all colleges in the nation.
I noticed that all of the colleges were listed twice.
How do I delete all of the duplicate records.

Here is my table.
Colleges
-------------------
schoolID - smallint NOT NULL,
schoolName - varchar(60) NULL

Can someone help me out with the sql statement???
I'm running SQL Server 6.5.

- ted

View Replies !   View Related
How To Find Duplicate Records
Hi,

As far as I know in SQL Server 6.5 there is no concept called rowid. How can I find duplicate records in a table and delete them.

Thanks,
Srini

View Replies !   View Related
Query To See Only Duplicate Records
How can I made a query to show only my duplicate records ?
For some reason that i do not know, i have duplicate entries in my clustered index 21 duplicate records in a table how can i query to know those 21 duplicate records ?

Thanks

View Replies !   View Related
Find Duplicate Records
Hi,

Does anybody know the SQL query to find the duplicate records?

Many Thanks in advance!

View Replies !   View Related
How To Find Duplicate Records
Hello board,

I was wondering if anyone can tell me an easy way to find duplicate records on sql. The thing is this, at work we have a database (table) which includes tracking numbers, I need a easy way to be able to search this table for duplicate tracking numbers and print them out. I currently access this table to edit some data by using the following path “Start > Programs > Microsoft SQL Server > Enterprise Manager” then work my down the tree to “Databases > Master > Tables” on tables I do a right click and “open table/query”. Any help would be most appreciated. Believe me I’m very “SQL illiterate”

Bill
:confused:

View Replies !   View Related
Delete Duplicate Records
Sorry for the new thread.

I have a userprofile table. There are a lot of duplicate records in this table. e.g.

USERID-----LASTNAME---EMAILADDRESS----CREATEDATE
----------------------------------------------------------------------
1----------A-----------A@yahoo.com---------2000-09-05 16:07:00.000
2----------A-----------A@yahoo.com---------2000-09-10 16:07:00.000
3----------A-----------A@yahoo.com---------2000-09-15 16:07:00.000

Userid is auto number, lastname and emailaddress are PK.

I want to delete duplicate records. If lastname and emailaddress are the same, only keep a record which createdate is the most newest date. See above example I only want to the record which userid is 3. I have alreday created a code which I attached below. This code onle keep a record which userid is 1.

Anybody can help me to solve this problem? Thanks.

============== My current code ====================
delete from userprofile where userprofile.userid in
--list all rows that have duplicates
(select p.userid
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1))
and userprofile.userid not in
--list on row from each set of duplicate
(select min(p.userid)
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1)
group by lastname, emailaddress)

View Replies !   View Related
Search Duplicate Records
Just like Unique/Distinct command, is these some way I could list just the duplicate records from a table . The field is numeric.
Thanks a lot for you help.

View Replies !   View Related
Remove Duplicate Records
hi, I have a table contains 3000 records, I ran this statement
select company_name,count(*) company_name
from vendor
group by company_name
having count(company_name)>1

This got me all companies and the duplicate counts, total
duplicate counts were 80. I need to remove the duplicate and
keep half of thoes companies...
how can I do so, please hlep

Thanks
Ahmed

View Replies !   View Related
Filtering Duplicate Records
hi,

I am trying to fetch data from 2 tables, say TABLE1 and TABLE2, both of which got columns like id and num. Then i want all the rows from TABLE1 where id1=id2 and num1 != num2.
but it is showing all the rows for an id1 twice, if there are two records in TABLE2 with same id and num.
is there any way to filter those records without using the distinct keyword.

regards
Rajeev.

View Replies !   View Related
Duplicate Records Query
Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table

customerid(PK)
accountno
fname
lname


Records will be

like this

customerid accountno fname lastname
1 2 lori taylor
2 2 lori taylor
3 1 randy dave


Email

emailid (PK)
customerid
emailaddress

View Replies !   View Related
Returning The Last Row From A Set Of Duplicate Records
Any information as to how to handle this?

Thanks.

View Replies !   View Related
Page 2 - Duplicate Records
okay, great, this is something to work on

let's start with the subquery called "d"

this subquery appears to have lost its UNION, but that's okay, you can add that back in later

the part that didn't work right is that you must reference the columns of "d" by their correct names, and these names are those that you assigned in the subquery

so it should look like this:
Code:

SELECT [WO Id]
, MAX([Want Date]) AS max_Want_Date
, SUM([End Qty]) AS Sum_End_Qty
, ...
FROM (
SELECT WORK_ORDER.DESIRED_WANT_DATE AS 'Want Date'
, WORK_ORDER.BASE_ID AS 'WO Id'
, WORK_ORDER.DESIRED_QTY AS 'End Qty'
, WORK_ORDER.PART_ID AS 'Part ID'
, NULL
, OPERATION.RESOURCE_ID AS Resource
, PART.DESCRIPTION AS Description
, NULL
FROM PART
INNER
JOIN (
WORK_ORDER
INNER
JOIN OPERATION
ON ...
) as d
GROUP
BY [WO Id]

does this make sense?

View Replies !   View Related
Excluding Duplicate Records
and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?

View Replies !   View Related
Ignore Duplicate Records
I am importing data into a SQL table and there is a potential for duplicate records to be coming in. How do I simply ignore the duplicates and add only the records that do not violate the keys?

View Replies !   View Related
Removing Duplicate Records
Dear All

This is query i have written is giving following output.

FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:11
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-205H1-67XS23124148/6/2007 19:36:10
R1-220H1-66F433365348/6/2007 19:30:27
R1-220H1-66F433365348/6/2007 19:30:29
R1-400H1-64ER53436648/6/2007 19:24:23
R1-400H1-64ER53436648/6/2007 19:24:26
R1-408H1-65TE4626268/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:25:00


I want the output like this,it should take only Min Datecreated
FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-220H1-66F433365348/6/2007 19:30:27
R1-400H1-64ER53436648/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:24:23

View Replies !   View Related
Finding Duplicate Records
Hello,

I searched for all the posts which covered my question - but none were close enough to answer what i'm trying to do. Basically, the scenario is thus;

Table1 contains values for UserID, Account code, and Date.

My query (below) is trying to find all the accounts assigned to a particular user ID, but also those duplicate account codes which belong to a second user ID. The date column would be appended to the result set.

The query I'm using is as follows;

select acccountcode, userid, date from dbo.table1
where exists (select accountcode from dbo.table1 where accountcode = table1.accountcode
group by accountcode
having count(*) > 1)
and userid = 'x-x-x'
order by accountcode

What I think this produces is a list of all files where a duplicate exists, but of course it leaves out the 2nd UserID...which is crucial.

Hopefully this makes sense. Any insight my fellow DBA's can share would be greatly appreciated!

Thanks,
D.

View Replies !   View Related
How To Check For Duplicate Records
I have this simple query But i want to check if the policy number appears more than once, I tried to use a count(RR.X_POLICY_NO)>1 in the where clause but i get an error, anybody have any suggestions?

QUERY:
Select
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR,


From RR
WHERE

year(rr.X_POLICY_XPRTN_DT)>=2005
Group By
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR
ORDER BY
rr.X_POLICY_NO

View Replies !   View Related
What To Update The Duplicate Records
hi,

i want to update the second row of the c column.can any one help me in this . this is the sample records.
a b c
001testNULL
001testNULL
005testNULL

View Replies !   View Related
Selecting Duplicate Records
Is there a way to see a list of duplicate records??
EG There is a field named "Invoice" in a table named "Orders" and I want to see only records where the same invoice shows more than once.

Sample output:

Invoice--Partno
123------a66
123------9pp
123------k33
5988-----j22
5988-----bx1
66-------pq1
66-------333

etc......

Thanks
Mike

View Replies !   View Related
Deleting Duplicate Records
gaurav writes "respected sir
here i have a question
how we can delete duplicate records through query in SQL Server
thanks"

View Replies !   View Related
Duplicate Records In The Database
i have duplicate records, but they have unique ids.

the duplicates resulted as a result of using a view to enter the data using BCP to load data from text file that had many dulicate records as part of consolidting the data i would like to get rid of the duplicate

is there some way of checking if the Name, Address, Telephone and Image are the same as on a record with another then remove them.

the table has the following colunms:

ID, Name, Address, Telephone, Image

1, adam, 45 tree gate road, 00000 000000, adam.jpg
2, uno, 44 garstang road, 00001 000001, uno.jpg
3, adam, 45 tree gate road, 00000 000000, adam.jpg
4, brian, belgarth house, 00022 000022, adam.jpg
5, karen, 3 chester close, 00002 000002, adam.jpg
6, uno, 44 garstang road, 00001 000001, uno.jpg
7, adam, 45 tree gate road, 00000 000000, adam.jpg


from the sample data you can see that there are multiple records is there a way of clensing the data using a sql command?

is there any issues such as performance on clensing it etc?

are there any strategies that should be implemented at the data tier so as to prevent duplication of data?

View Replies !   View Related
Delete Duplicate Records
Hi,
Please help me in constructing a query that will delete duplicate records in a table;
Please see my table structure below:

CREATE TABLE LATEST_DATA (
[ID] int NOT NULL,
[IPage] varchar(100) NULL,
[IDevice] varchar(255) NULL,
[IGroup] varchar(255) NULL,
[IField] varchar(255) NULL,
[IValue] varchar(255) NULL,
[IIcon] int NULL,
[IID] int NULL,
[ReportID] int NOT NULL,
[RVersion] varchar(255) NULL,
[RHost] varchar(255) NULL,
[RUser] varchar(255) NULL,
[RLocation] varchar(255) NULL,
[RDateTime] varchar(16) NULL,
[RComplete] bit NOT NULL
);

-- Insert Latest data
INSERT
INTO LATEST_DATA
SELECT
[Item].[ID],
[Item].[IPage],
[Item].[IDevice],
[Item].[IGroup],
[Item].[IField],
[Item].[IValue],
[Item].[IIcon],
[Item].[IID],
[Item].[ReportID] ,
[Report].[RVersion],
[Report].[RHost],
[Report].[RUser],
[Report].[RLocation],
[Report].[RDateTime],
[Report].[RComplete]
FROM
[dbo].[Item],
[dbo].[Report]
WHERE
[Report].[ID] = [ITEM].[ReportID] AND
[Report].[ID] = ( SELECT
MAX([Report].[ID])
FROM
[dbo].[Report]
);


thanks

View Replies !   View Related
How Can I Delete Duplicate Records
Hi Guys,

I have the following table
customerid customername
------------------------
1 AAA
1 AAA
2 BBB
2 BBB
2 BBB
3 CCC
3 CCC

Here, I need to delete duplicate records from the above table.
After deleting the duplicate records the table should be
like this:
customerid customername
------------------------
1 AAA
2 BBB
3 CCC


Can any one help me!!!!!!

Regards
js.reddy

View Replies !   View Related
Eliminating Duplicate Records
Hey There.

I'm in the process of doing a major data clean up and I'm just wondering how I would go about eliminating some redundant data.

The Table Layout

Contracts

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A


Equipment

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 1 A
3 DEVICE4 3 A


You will notice that each customer may have multiple devices. Each device may be tied to a contract, and each contract may have one or more devices tied to it.

In the example above, you will notice in the contracts table the contracts with the IDs 0 and 1.

Fig 1.

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A


These contracts have the exact same information.

Furthermore, if you look down the table you will notice the contract with the ID 3.

Fig 2.

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
3 1234567 081205 A

This contract shares the same contract and customer number, but has a different start date.


Now lets take a look devices in the equipment table that refer to these records.

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
2 DEVICE3 1 A
3 DEVICE4 3 A

You will notice that DEVICE1 and DEVICE 3 refer to the contract records that contain identical data. (As shown in 'Fig 1')

My question is as follows:

How do I eliminate the any duplicate records from the contracts table, and update the records in the equipment table with id of the left over contract.

Results Should be as follows:

Contracts

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A


Equipment

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 0 A
3 DEVICE4 3 A


Any help you may provide would be greatly appreciated!

Thanks
--mike

View Replies !   View Related
Delete Duplicate Records
Hello everyone,

I have a little dilemma. I have a table ALLTABLE that has duplicate records and I want to delete them. ALLTABLE has these columns with these values for example:

Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 25476 63 31 2
ADC-WC-0010005-0 25476 63 31 3
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
ADC-WC-0010344-0 515 63 01 2

As you can see there is some duplicates in the first 3 rows and the final 2 (the entity number is the only difference). I want the table to look like this:

Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1

Thank you so much for the help. It is really appreciated.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved