Select Records That Do Not Duplicate On A Certain Field.
Mar 15, 2004
I am looking for some help in pulling certain people out of a table
This is the basic setup of my table
PK ID NAME MEETING
1 11111 Joe CLASS98
2 22222 Jane CLASS98
3 33333 Bob CLASS98
4 11111 Joe CLASS04
5 22222 Jane CLASS04
6 44444 Sally CLASS04
What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04
I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember]( [dimMemberId] [int] IDENTITY(1,1) NOT NULL, [dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)), [MemberCode] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](250) NOT NULL, [LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters) Last Name DOB CurrentAddress1 MemberCode SSN SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email --------- --------- <any> abc@abc.com <any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
I have a table like TradeID ActionID 58096 3663 58096 3664 78901 2235 78901 2236
I want to select the only the TradeID with the highest ActionID
I tried using select distinct tradeid,actionid From cct Where ActionID = (SELECT MAX(ActionID) FROM cct1 WHERE cct1.TradeID = cct.TradeID) group by tradeid,actionid
I have a requirement to mark duplicate records when I pull them from the database. However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one. The code I have below creates a column called Dupes but marks all the duplicates - including the first one. Is there a way to only mark the 2nd, 3rd, 4th etc record ? SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber, Dupes = (select count(id) from CallLogswhere (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21), FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'
I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.
The table which stores the company information lists it in multiple rows, so there will be many results for each user user -- companyid johnd -- 2 johnd -- 5 johnd -- 1
I want to select this into one record so it will show up like this user -- companies johnd -- 2,5,1
The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
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.
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 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.
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?
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?
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
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
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
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 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.
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
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)
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.
