How Can I Remove Duplicate Entries In A Sql Query?
I have a database being populated by hits to a program on a server.
The problem is each client connection may require a few hits in a 1-2
second time frame. This is resulting in multiple database entries -
all exactly the same, except the event_id field, which is
auto-numbered.
I need a way to query the record w/out duplicates. That is, any
records exactly the same except event_id should only return one record.
Is this possible??
Thank you,
Barry
View Complete Forum Thread with Replies
Related Forum Messages:
Remove Duplicate Entries
I am a newb at ms sql and was hoping someone could help me eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because the duplicates are from the PRODUCT.PRODUCT that have more than 1 image. If anyone could rewrite this statement so I can learn from this, it would be most appreciated! Thank you for your time <asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>
View Replies !
How To Remove Duplicate Rows From Full Join Query
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com 1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com 1 2 xyzabc NULL NULL 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL Now, I want unique rows from the above result set like :- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
View Replies !
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 1 Java Overall a@b.com 2008-01-27 14:44:29.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 I want the result set as follows:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View Replies !
How To Remove Dup Entries
Hi All In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries I ant to remove the dup entries and i want to place in the table. Waiting for valuable replies Thank u Baba
View Replies !
Duplicate Entries In SQL W/ IE 5.5
I am getting duplicate entries in SQL database(7.0 or 2000)when users running IE 5.5 or higher access my ASP pages. We are running IIS 4.0, but the problem occurs with IIS 5.0 also. I'm finding no information on this problem...has anyone else had the same experience? Thanks
View Replies !
Duplicate Entries
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated? Thanks
View Replies !
Duplicate Entries
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem. The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1. We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening? Here is how I create the new record id: I get the MAX(ID) from the table I add 1 to the ID and then insert a new record with the new ID into the table. Any help is appreciated. Thanks, enak
View Replies !
Duplicate Entries
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script: Select L.URN, LH.THE_TIMESTAMP, LH.ACTION_TYPE, LH.ACTION_BY, LH.ACTION_REQD, LH.NOTE, em.position_type_ref From LOG L Join Log_history LH on (L.URN = LH.LOG_URN) left outer join employee em on (em.code = LH.action_by) Where (L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU') Order By L.URN ASC, LH.THE_TIMESTAMP DESC The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
View Replies !
Need Your Help To Remove Spaces In The Column Entries Using SQL
Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance
View Replies !
Duplicate Entries In A Field
I have a field called RegId. RegId is of datatype NVARCHAR (20). RegId ----- 12322 2122111 23423 etc etc I want to run a query to find out if there are duplicate entries in this field. Any ideas on how I can achieve this? Thanks in advance, Anthony
View Replies !
Dont Sum Duplicate Entries
Hi. I have a table with Login and Logoff Time of users, but there could be duplicate Logtimes in the dataset, but for different products. Because of this I cant do a distinct in the dataset. I need the Product and some other details in my Report. I tried to make two datasets. One for the Select distinct and one for the other. But the Problem is: in my report, I need a table, where I make the Sum of the Logintime a day and in another column I calculate with data from the other dataset.(Logtime + data from dataset2). But this doesnt work, so I think, that is it not possible to join 2 dataset in one table. datetime Login | datetime Logout | Product 11.12.2007 10:15 | 11.12.2007 12:15 | p1 11.12.2007 10:15 | 11.12.2007 12:15 | p2 11.12.2007 12:19 | 11.12.2007 15:15 | p2 Is there another option I can do this?
View Replies !
Duplicate Entries In The Resulting Table
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks. Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins] @Userid uniqueidentifier OUTPUT,@DisplayName varchar(200) AS SELECT * FROM dbo.UserProfile INNER JOIN dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date Return
View Replies !
Preventing Duplicate Database Entries
Hi all.. I've been scouring the forums for about 6 hours to no avail. This is a really simple question. I'm trying to have a registration page that lets the user input name, email, desired username, and password. I want to check the username and email fields to make sure ppl cannot sign up twice. So from what I've gathered I have a couple of options: 1) i can set up a unique constraint on the database columns, 2) i can run a select statement before inserting, 3) i can store the whole database column in a variable then search through it. My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
Duplicate Backup Entries Using SQLMAINT
I would appreciate someone pointing me in the right direction to resolve a backup anomaly were currently experiencing. We recently installed SQL Server 6.5 and noticed that although our scheduled tasks were running as requested the DelBkUps parameter wasn`t working. In addition we noticed that the backup files that were created didn`t have the time portion appended to date (suffix of backup name). The steps we took to resolve this was to install Service Pack 3. After the install the following was observed: 1) DelBkUps parameter started to work 2) duplicate backup entries were created, the only difference being that for one of the entries the time portion was still missing (ie. apps_db_dump.19980722 instead of apps_db_dump.199807221840). Letting in cycle through for a week didn`t have any affect. The final observation is that for scheduled tasks that occur more than once/day (i.e. transaction dump every 8 hours) no duplicate backup entries are created and file suffix is correct (i.e apps_db_dump.199807221840). .......thanks,,,,brad .............
View Replies !
Removing Duplicate Entries In SQL Field
Hi All, Below is a snippet of MS SQL inside some VB that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense. Below is the SQL and the results from the returned page. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
View Replies !
Stop Inserting Duplicate Entries
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis. INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD) (SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B)) When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax. Can anybody help me out?
View Replies !
Truncating Duplicate Entries In A Table
Hi, I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it. Can anyone provide me the syntax for the same? I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX. Please help me out...
View Replies !
Finding Duplicate Entries (with Different Keys)
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name. How could you query this: ID fname lname 10001 Bill Jones 10002 Joe Smith 10003 Sue Jenkins 10004 John Sanders 10005 Joe Smith 10006 Harrold Simpson 10007 Sue Jenkins 10008 Sam Worden and get a result set of this: ID fname lname 10002 Joe Smith 10005 Joe Smith 10003 Sue Jenkins 10007 Sue Jenkins
View Replies !
Duplicate Entries In Returned SQL Field
Hi All ! Below is a snippet of MS SQL inside ASP that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS
View Replies !
Check For Duplicate Entries In One Field
Obviously, I'm a complete n00b at SQL. I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID). I'm trying to make the first field my primary key, so I need to fix these duplicate entry. I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly. Thanks in advance for what is hopefully a simple answer.
View Replies !
Best Method Of Checking For Duplicate Entries In SQL Server
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional. In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives. 1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table. 2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique. Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice? TIA for your help.
View Replies !
Writting Trigger Or Procedure To Delete Duplicate Entries In A Table?
I am using Sql Server 2000. I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone. I used to insert entries in this table from an excel file. One excel file will contain thousands of customer. In this table combination of StdCode and Phone should not be repeated. If I do it in my VB.Net coding.then application gets drastically slow. So I want to write a procedure or trigger for this. Here what I will do, I will send all records into database then this trigger or procedure will check for any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry or will not allow this new entry. Is this possible to do using Trigger or stored procedure?
View Replies !
Remove Duplicate
HI All, I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates.. Thnx ;) Shani
View Replies !
REMOVE DUPLICATE ROWS
Hi everyone.How can I get the unique row from a table which contains multiple rowsthat have exactly the same values.example:create table test (c1 as smallint,c2 as smallint,c3 as smallint )insert into test values (1,2,3)insert into test values (1,2,3)i want to remove whichever of the rows but I want to retain a singlerow.TIADiego
View Replies !
Remove Duplicate Rows
I've got the following table data:116525.99116520.14129965.03129960.12129967.00And I need to write a query to return only rows 2 and 4, since theremaining rows have duplicate IDs. I've tried the Group By, but amhaving no luck.Thanks!
View Replies !
Remove Duplicate Data
I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with ID Name Add1 Add2 1 Matt 16 Nowhere St Glasgow 1 Matt 16 Nowhere St Glasgow, Scotland 2 Jim 23 Blue St G65 TX 3 Bill 45 Red St 3 Bill 45 red St London The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas? Cheers
View Replies !
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 !
Remove Duplicate Rows
Hi Folks: I'm in the process of data conversion. Before bcp to the actual table, I create another intermediate table with same record structure, different name and without any indexes,foreign key constraint .... so that I can bcp the ASCII data set into that table with no error. Now, is it possible to use one SQL statement to remove ALL DUPLICATE ROWS BUT THE LAST ONE. I meant, say that table t has 10 duplicate records on that fiel F1, I want to delete 9 of them but keep only one record only? Thanks in advance for your time and advice. David Nguyen
View Replies !
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 !
How To Remove Duplicate From CSV Using SSIS
Hi All , I have a CSV file which contains some duplicate record and i have to load this file in SQL server database using SSIS package . What i have to do is read the file and if the same record entry is occur more than 10 times for a particular unique combination ( like ID , Date , Time ) then i need to take only one record for that occurance. Plesae suggest , Help , Regards, Ashish
View Replies !
Faster Remove Duplicate SQL
I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates: Code Snippet DELETE FROM _email WHERE _email.eid IN ( SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) ) AND _email.eid NOT IN ( SELECT Min(eid) FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) GROUP BY emailaddress ); This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster? I am running SQL Server 2000 Thanks in advance
View Replies !
Remove Duplicate Rows From Table
I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove... my tables is like that Names ------------ Rakesh Rakesh Rakesh Kumar Sharma Rakesh Kumar Sharma Baburaj Raghu Raghu and Output of query should be like that Names ----------- Rakesh Rakesh Kumar Sharma Baburaj Raghu Thanks in advance
View Replies !
Remove Duplicate Records From A Tble
hi, I run this script and found duplicate records. how can I delete all rows that have more than one record but Still keep one from each duplicated record SELECT SALES_CITY,ORDER_NO,CIRCUIT_ID ,COUNT(*) as countrows FROM TCOMS_ACC_WEEKLY GROUP BY SALES_CITY,ORDER_NO,CIRCUIT_ID HAVING COUNT(*) >1 SALES_CITY ORDER_NO CIRCUIT_ID COUNTROWS ---------- -------- -------------- ----------- alb C0000322 3ma04 a12 0001 3 alb C0000398 13a04 a04 0001 2 alb C0000398 13a04 a04 0002 2 alb C0000398 13a04 a04 0003 2 I got 1717 row(s) duplicate, I need to keep only one record from each duplicate. so I can create a primary key on( SALES_CITY,ORDER_NO,CIRCUIT_ID )after I delete the duplicate. thanks for your help Ahmed
View Replies !
Remove Duplicate Rows From A Table
Hi guys I have been using SQL server 2005. I have got a huge table with about 1 million rows. Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that?? Is there a query in SQL to remove duplicate rows??? thanks Mita
View Replies !
Filtering/Grouping To Remove Duplicate Values...
There's some SQL below (T-SQL) & I'm wanting to have this result set grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column. The columns BCOM_ID contain unique values, but Venue_ID can have duplicate values. I only want to get rows for one instance of the Venue_ID (per BCOM_ID) - doesn't matter which instance but basically, no duplicates. Oh yes, one of the columns is a Bit column. Any ideas would be welcome & appreciated! Many thanks, Darren darren@darrenbrook.fsnet.co.uk SQL:- SELECT Booking_Header.BH_ID, Booking_Header.Booking_Header_Description, Booking_Header.BStat_ID, Booking_Header.BT_ID, Booking_Header.Tagged, Booking_Header.Status_Timestamp, Booking_Header.Start_Date, Booking_Header.Days_Qty, Proposal.PPL_ID, Proposal.PPL_Status, Booking_Component.BCOM_ID, Booking_Component.Component_Description, Booking_Component.Venue_ID, Venue.Venue_Code, Venue.Description, Address.Address_ID, Address.Town, Booking_Status.BStat_Description, Booking_Type.Type_Description FROM dbo.Booking_Header INNER JOIN dbo.Proposal ON dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN dbo.Booking_Component ON dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER JOIN dbo.Venue ON dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN dbo.Address ON dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN dbo.Booking_Status ON dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER JOIN dbo.Booking_Type ON dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID WHERE (dbo.Proposal.PPL_Status = 1) AND (dbo.Booking_Header.BH_ID = 10) Thanks, Darren
View Replies !
Finding Duplicate Entries In A &"smart&" Way - By Comparing First Two Words
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"
View Replies !
Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them. each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field. Ideas? Thanks, Ad.
View Replies !
Update All Entries In 1 Query
hi, i have a table where i need to update all entries (columns/rows) in 1 queryto make this one clear, if i have table_id firstname lastname1 clayton111 dylan1112 phoebe111 miller111 from the above example, i just want to eliminate all the 111 from the firstname, lastname fields, however, im not sure how to do that in a single query. i asked this because i have like 100 rows and each entries have "111" and i need to remove those. thanks
View Replies !
Getting Distinct Entries In Query.
Hello, I have a reference table. This table has an effective date and end date, and I need to get a single set of values at a point in time. Suppose the following hotel rating values are the values that I want to pull out of the table: 1 Star2 Star 3 Star 4 Star 5 Star This value has other information associated with it (benchmarks and such) and so it's possible that one of the 2 star entries gets end-dated, and then a new 2 Star entry gets created with an effective date and no end date. How can I query so that I only get one 2 Star entry at any point, and get the point in time where the date is between the effective and end date, but only pull back a single set of entries, one distinct rating value. So, suppose the first entry is end-dated 7/1, if I look at old data, I want to see the old benchmarks and so I want the previous entry end-dated 7/1; otherwise, I want to see the new end-date, with the new 2 star entry with no end date. How do I do that? Make sense?
View Replies !
Query Highest Entries From Database - Pls Read More.....
Hi folks, sorry for the poor explanation. Im using SQL 2000 I have a database that has a column named 'Initials' in a char in field I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this Initials Count DRT 51 AMS 49 JJJ 21 PLI 10 Hope u can help, thanks in advance
View Replies !
How To Remove Quotes From A Query
Hi, i have a query like select * from table1 where @variable1 and variable1 id holding the value id=1 so what i want is select * from table1 where id=1 but here the values variable1 is passesed from a c# program which encloses the vaule within single quotes. so what i get is select * from table1 where 'id=1' how to correct this? hope my question is clear. Thanks
View Replies !
Need To Remove Extra Spaces In Query Output
I'm using the following command: osql -E -n -d testdb -i testquery.qry -o "c:Scriptsoutput.txt" -h-1 -w 500 -s "," With the following query (testquery.qry): SET NOCOUNT ON SELECT table1.column5, table2.column9 FROM table1, table2 WHERE table1.column4 = table2.column4 AND table1.column1 != "NULL" ORDER BY table1.column5 All of the columns are cast as char up to 50 characters. Even if only a field has a few characters, I get a lot of extra white space in my output. I want to get rid of those trailing spaces. I've tried SET ANSI_PADDING OFF, RTRIM(), and CAST(x AS VARCHAR(y)). I still get the same output. What am I doing wrong, what am I missing?
View Replies !
|
TRACKER
