Select Duplicate Rows And Average Them.
I have a table with four columns: id, value1, value2, and name. I need to select all duplicate rows with the same name in the name field. Once selected I need to average the value1 and value2 rows with each other. Then I would like to delete the duplicate rows and replace with the new averaged values. Should I first move the duplicate rows to a temp table and then average? Any help would be appreciated.
View Complete Forum Thread with Replies
Related Forum Messages:
Select Distinct Rows From Duplicate Rows....
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View Replies !
Select Duplicate Rows
I get a tabseparated textfile with data every friday. Faulty rows are to be returned to the source. Double rows are considered a fault. How do I select all double rows? I join the data later on with four columns so if these columns are alike the rows are considered identical even if other columns are not. Ie: A, B, C, D, 12, 34, 48, 76 A, B, C, D, 23, 45, 56, 99 These two rows are considered alike since I join on A+B+C+D. I'd like to select ALL double rows and insert them into a separate table that I can return to the source. Does anyone have an idea how to do this?
View Replies !
Help With Select To Return Duplicate Rows
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address FROM dbo.tblClient INNER JOIN dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address HAVING (COUNT(dbo.tblClientAddresses.Address) > 1) ORDER BY dbo.tblClientAddresses.Address
View Replies !
Select Duplicate Rows In Legacy System
Hi.., There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome... Thanks in advance..
View Replies !
Help With MDX To Average Number Of Rows At All Levels
Hi gang, I have a cube that has two dimensions (DimCustomer and DimItem), and one fact (FactShop). FactShop looks like this: id (PK) customer_key item_key style_id and is a many-to-many between customers and items. Here is what I need. I want my rows to have these values nested (drillable): Cust.Shape Cust.Size Cust.Height All three of those are in the DimCustomer table, and I can organize the rows to get this with no problem. The columns I want just Item.Type. Easy-peasy. Now, at the intersection (the cells), what I need is the average number of items for that level. Let's say there are 300 Customers with Shape X, 200 Customers with Shape X and Size 5, and 100 Customers with X, 5, and Height Regular. Each Customer has some number of Items (via the FactShop table), and let's say Item.Type "Coat" has 3000 at the Shape-X level, 1200 at the X-5 level, and 100 at the X-5-Regular level. I want to see (in the cells): 3000/300 = 10 1200/200 = 6 100/100 = 1 I cannot for the life of me get this to work... I have tried calculated measures, calculations, everything. I either get errors I do not understand (such as trying to create a named calculation in the DSV using "AVG(COUNT(DISTINCT style_id))" on the FactShop table, or errors in the calculation when I try to make a new MDX calculation. It seems to me that I need an MDX query to do this. Can someone tell me if I am on the right track, and if so, how to construct the MDX to do this? Thank you VERY much! Chris
View Replies !
Calculating Average From Rows Entries..
Dear all, I havea table name HISTORY_MEASURE which is a collection of different measure value issue from different mesuring device. Values inthis table is represented as follow : Id Name Value ============================== 1 Diameter1 0.45 2 Diameter2 1.23 3 Temperature2 123 4 Temperature2 200 5 Diameter1 0.65 Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course. So I I take the example of Diameter1 I need to calculate and display in a field the average results. The result would be Name Average ===================== Diameter1 ..... Diameter2 .... Temperature2 .... Temperature1 .... How can I perform this ? Or could it be better to get a view of the table above which gets display as follow : Diameter1 Temperature2 Diameter2 0.45 123 1.23 0.65 200 0 Thanks fro your help regards serge
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 !
Returning Average Of Multiple Rows In A Table Join
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
View Replies !
Using MDX To Find An Average For Leaf Members On Rows...dynamically
I am trying to find the average number of products of the set that ison rows...dynamically.For example, if I had states on rows and was looking at the averagenumber of products, I would see thisIN 72MI 66AL 88Using the code below, these numbers are found by taking the average ofthe number of products of the children of the members of the currentlevel of the current dimension. For example, the children of IN mightbe Indianapolis (number products 73) and Evansville (number products71)..and the average of these two cities' number of products is 72:avg(Dimensions(StrToSet("Head(Axis(1))").Item(0).Item(StrToSet("Head(Axis(1))").Item(0).Count- 1).Dimension.Name).CurrentMember.Children, [Measures].[NumberProducts])That works great. However, what if you are at the leaf level? Forexample, what if I select multiple members of the township level ofthe location dimension? If I select Barr Township and LaCrosseTownship from the long list of townships of Indianapolis, and put thaton rows...what if I want to see the average of those 2 townships? Forexample, when looking at the value of the number of products I see:Barr Township 22LaCrosse Township 44Then when I am looking at the average number of products I want to seethis:Barr Township 33LaCrosee Township 33How can I do this with MDX?
View Replies !
Help Needed With Calculating Average For Both Parent And Child Rows
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help. Select CustomerCorrection.Id As ParentId, cc_company As cc_company2, ccError.Id As ErrorId, COALESCE(cic_Group,'Other Errors') As cic_Group, COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription, DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge From CustomerCorrection Inner Join CCError On CustomerCorrection.Id = CCError.CustomerCorrectionId Left Outer Join CustomerImportControl On cic_code = ce_errno Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228) Order By CustomerCorrection.Id That Returns 129 IWA 992 Other Errors Unknown Unknown 399.500000 129 IWA 1089 Other Errors Unknown Unknown 399.500000 129 IWA 1760 Other Errors Unknown Unknown 399.500000 1004 IWA 952 Other Errors Unknown Unknown 365.333333 1004 IWA 1853 Other Errors Unknown Unknown 365.333333 3228 IWA 10 Other Errors Unknown Unknown 329.375000 Here is my temp workaround: Select a.*,b.* From ( Select cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge From CustomerCorrection Group By cc_Company ) As a Inner Join ( Select cc_company As cc_company2, COALESCE(cic_Group,'Other Errors') As cic_Group, Count(Distinct ccError.Id) As ErrorRecCount, AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge From CustomerCorrection Inner Join CCError On CustomerCorrection.Id = CCError.CustomerCorrectionId Left Outer Join CustomerImportControl On cic_code = ce_errno Group By cc_company, COALESCE(cic_Group,'Other Errors') ) as b On a.cc_company = b.cc_company2 Order By cc_Company,cic_group
View Replies !
SELECT Average Values FROM Another Table
So I have this table called "listings"... there are 100 unique listings with an integer ID for each. I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10. I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing. I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all, This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function. I have 3 dbo tables in SQL Server Management Studio Express (SSMSE): dbo.Projects: ProjectID ProjectName LabName 1 Blueriver ALSI 2 Greentree GPL 3 Redrock STL-NJ NULL NULL NULL dbo.Samples: SampleID SampleName Matrix SampleType Chemical ProjectID 1 Blueriver01 Water Primary VOCs 1 2 Blueriver02 Water Duplicate VOCs 1 3 Blueriverr03 Water QA VOCs 1 4 Greentree11 Soil Primary VOCs 2 5 Greentree12 Soil Duplicate VOCs 2 6 Greentree13 Soil QA VOCs 2 NULL NULL NULL NULL NULL NULL dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 2 Benzene 25.60 ug/L 1 3 Trichloroethene 13.00 ug/L 1 4 Xylenes 0.00 ug/L 1 5 Acetone 90.70 ug/L 2 6 Benzene 31.40 ug/L 2 7 Trichloroethene 19.20 ug/L 2 8 Xylenes 2.00 ug/L 2 9 Acetone 140.30 ug/L 3 10 Benzene 21.50 ug/L 3 11 Trichloroethene 22.20 ug/L 3 12 Xylenes 0.00 ug/L 3 13 Acetone 222.10 ug/Kg 4 14 Benzene 10.30 ug/Kg 4 15 Trichloroethene 30.20 ug/Kg 4 16 Xylenes 50.70 ug/Kg 4 17 Acetone 211.90 ug/Kg 5 18 Benzene 16.40 ug/Kg 5 19 Trichloroethene 34.70 ug/Kg 5 20 Xylenes 60.00 ug/Kg 5 21 Acetone 220.30 ug/Kg 6 22 Benzene 13.20 ug/Kg 6 23 Trichloroethene 32.00 ug/Kg 6 24 Xylenes 55.50 ug/Kg 6 NULL NULL NULL NULL NULL The Average of chemical/analyte in 2 samples is defined as: Average = Abs(the result of primary sample + the result of duplicate sample)/2. Average = Abs(the result of primary sample + the result of QA sample)/2. Average = Abs(the result of duplicate sample + the result of QA sample)/2. The RPD of chemical/analyte in 2 samples is defined as: RPD = Abs(the result of primary sample - the result of duplicate sample)/ Average RPD = Abs(the result of primary sample - the result of QA sample)/ Average RPD = Abs(the result of Duplicate sample - the result of QA sample)/ Average I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples: (i) Between the primary and duplucate samples (ii) Between the primary and QA samples (iii) Between the duplucate and QA samples. In the Table "dbo.Projects", ProjectID is the primary key. In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key. In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key. I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)). For example: I do not know how to designate the following 2 results: dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 5 Acetone 90.70 ug/L 2 Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD. Thanks in advance, Scott Chang
View Replies !
DUPLICATE ROWS
I used the following select statement to get duplicate records on Case_number column select cases.distinct case_link, cases.case_number from cases group by case_link having case_number > 1 I got the error message that "'cases.warrant_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. AND cases.case_number' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Any idea on a better statement to use. THANKS FOR YOUR HELP!
View Replies !
Duplicate Rows
Hi, I have a table and this is what i did to get the desired result Select A.col1,count(A.col1) from Tab1 group by col1 having count(A.Col1) > 1 i tried this - but it didnot worked - it returned col1 as blanks - Select A.col1,B.Col2,count(A.col1) from Tab1 A, Tab2 B where A.col1 = B.col1 group by A.col1 , b.col2 having count(A.Col1) > 1 As I was looking for all the rows that are apperaing more than once. Now - The problem - I have to join this table to another table Tab2 to get the other details. My Tab2 is a table from where I have to pull the Customer DEtails like name,address etc. How should I write this query? Any thinuhts? TIA
View Replies !
Duplicate Rows
Hi, I've a query which gets a set of data from multiple tables - select * FROM A inner JOIN q ON (RIGHT(q.name,CHARINDEX('-',REVERSE(q.name))-1)= a.id) inner JOIN t ON (t.id = q.id) inner JOIN s ON (q.name = s.name ) inner join l on (s.name = l.name and t.name = l.name) WHERE A.id = 764 and s.name = '764' I get repeated # of rows for each id. I've some 136 rows for each q.id ( there are 6 q.ids and hence I get 816 rows instead of 136) These 136 rows are actually divided among thse q.ids as id=5, 4 rows id=6, 8 rows id=7, 24 rows id=8, 40 rows id=10, 60 rows total=136 rows Let me know what I'm missing here Thanks for your help! Subha
View Replies !
Duplicate Rows
Hi. I'm a SQL Server newbie, very experienced with Access, developing an ASP.NET database editor web app. I query the database with a statement more or less in the following form: SELECT organisation.OrgID, organisation.Name, organisation.whatever FROM services INNER JOIN servicegrouping ON services.serviceID=servicegrouping.serviceID INNER JOIN organisations ON servicegrouping.OrgID = organisations.OrgID WHERE services.service=x OR services.service=y In other words, I have a database of organisations. The services offered by the organisations are in a separate table, and I only want to return organisations that offer services X or Y. Okay, now if I did this in Access, this query would return just one record for each organisation that meets the condition, unless I was to include a field from the services table in the SELECT clause, in which case of course I would get one record for each organisation and unique service offered. But in MS SQL, the query returns duplicate rows if there is more than service offered by the organisation that meets the WHERE condition (=x or =y). Why is this and what do I need to do to my SQL statement to ensure I only get unique rows?
View Replies !
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second. What type of average are you seeing on your production machines? What is a normal average? I have tried researching for suggested values on these two counters, but I haven't turned up any information. Thanks, MV
View Replies !
Deleting Duplicate Rows
I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten. There is no Primary Key in the csv file that can be used. I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates. When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want. Any thoughts as to why this needs to be run twice? Or is a better approach available? Here is my code - SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*) INTO temppkgactions FROM pkgactions GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1 DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 ) FROM dbo.pkgactions DROP TABLE temppkgactions Thanks
View Replies !
SQL Combining Duplicate Rows, Please HELP.
Hello, I have a question, what does a statement look like that finds the duplicate rows and combines them, I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number. I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine. Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish. I have the following SQL statement: SELECT part_number, COUNT(part_number) AS NumOccurrences FROM Products GROUP BY Part_number HAVING COUNT(part_number) > 1
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 !
Merging Duplicate Rows
Hello All,I have an issue with dupliate Contact data. Here it is:I have a Contacts table;CREATE TABLE CONTACTS(SSN int,fname varchar(40),lname varchar(40),address varchar(40),city varchar(40),state varchar(2),zip int)Here is some sample data:SSN: 1112223333FNAME: FRANKLNAME: WHALEYADDRESS: NULLCITY: NULLSTATE NYZIP 10033SSN: 1112223333FNAME: NULLLNAME: WHALEYADDRESS: 100 MADISON AVECITY: NEW YORKSTATE NYZIP NULLHow do I merge the 2 rows to create one row as follows:via SQL or T-SQLSSN: 1112223333FNAME: FRANKLNAME: WHALEYADDRESS: 100 MADISON AVECITY: NEW YORKSTATE NYZIP 10033Pointers appreciated.Thanks
View Replies !
Top N Rows, Discard Duplicate
I have data in table as follows,NumCategoryProductName---- -------- -----------100AProduct1100BProduct1101AProduct2101BProduct2101AProduct2101BProduct2102AProduct3102BProduct3102CProduct3102DProduct3Can sb pl help to what t-sql query to use to achieve as follows,for some reason, there are duplicate rows, but i need to gettop most rows of category A, B as follows.NumCategoryProductName---- -------- -----------100AProduct1100BProduct1101AProduct2101BProduct2102AProduct3102BProduct3TIAMeDhanush
View Replies !
Duplicate Rows But No Key On The Tables
Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther
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 !
Suppressing Duplicate Rows
Hi,I need to get a list of columns and corresponding datatypes for thegiven table name. I am using the query:selecta.name,b.type_namefromsyscolumns a,master.dbo.spt_datatype_info b,systypes c,sysobjects dwherea.xusertype = c.xusertype andb.ss_dtype = c.xtype anda.ID = (SELECT OBJECT_ID(<tablename>)) anda.ID = d.ID andd.ID = (SELECT OBJECT_ID(<tablename>))This returns multiple sets of data for each column. This is becausethe same column name is repeated in multiple tables in primarykey-foreign key relationships. Is there a way in which I can get onlyone row for each column of a table?
View Replies !
Delete Duplicate Rows
Suppose that we have the following rows in a MSSQL table : 1administrateur1NULLNULL 2administrateur2NULLNULL 1administrateur1NULLNULL 2administrateur2NULLNULL How to remove duplicates (leave only the 2 first rows) ?
View Replies !
Delete Duplicate Rows
Hi, I have the following query to select duplicate rows from the table. How can i delete them with out using temp table. select UserName, Title, Name, ColWidth, Sequence from table1 (nolock)) Group by UserName, Title, Name, ColWidth, Sequence Having count(*) >1 Any help would be greately appreciated. Thanks
View Replies !
Removing Duplicate Rows
Hi, Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing. Table : create table test(col1 int, col2 text) go insert into test values(1, 'abc') go insert into test values(2, 'abc') go insert into test values(2, 'abc') go insert into test values(4, 'dbc') go Please advise, Thanks, MiraJ
View Replies !
Fetching Duplicate Rows
Hi, I have some duplicate rows in a table. I didnt define any primary key or unique key on the table. I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows. How can i do it? Please help me..... Thanx in Advance
View Replies !
Don't Insert Duplicate Rows
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3. This the table1: create table table1( col1 int not null, col2 int not null, col3 int not null, constraint PK_table1 primary key (col2, col3) ) This is my "insert" code: INSERT INTO table1 SELECT table2.col1,table2.col2, table3.col3 FROM table2, table3 WHERE table2.col1 = table3.col1 Wich conditions shoud i add to this code? Thanks. fmilano.
View Replies !
How To CREATE Duplicate Rows
this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition. using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id. but is there any one to perform this action just using sql? thanks, goran.
View Replies !
Problem With (almost) Duplicate Rows
Hello, I have a table (mytable) with the following structure docs int field1 varchar(20) ...... the information in the table may look like this docs field1 1 hello 2 hello 3 test 4 test 5 problem 6 problem The docs column autoincrements and their is a unique constraint on it. The field1 column does not have any constraints on it. how does on delete the duplicates without deleting both. I can write a SQL statement to tell me what docs are dups, and what the field1 values are, but I cannot just delete one Do I write a cursor? or is there an sql statement that would delete just one? thanks Steve Power
View Replies !
DELETE DUPLICATE ROWS
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW. THANKS JK
View Replies !
Deleting Some Of Duplicate Rows
This is an imaginary problem while discussing ROWID in ORACLE. Consider a table without primary key, unique key, uniuqe index. A row has inserted into the table many times. I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated) will be deleted. In ORACLE i can achieve this using ROWID as follows: Delete from Table_name where < all column values > and ROWID <> ( Select max(rowid) from Table_name where < all column values > ) How can this be achieved in MS SQL Server 6.5 ? According to Dr. Codd's Golden rules for RDBMS one is that One should be able to reach each data value in the database by using table name, row idenfication value and column name. Does MS SQL Server 6.5 satisfy this requirement ? Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5 Satisfy? Which doesn't ? Any discussion about Codd's Rules is welcome. - Gunvant Patil gunvantp@yahoo.com
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 !
Deleting Duplicate Rows
Hai I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error. The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99' If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time. Thanks
View Replies !
Duplicate Key But Unique Rows.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. Randy Garland
View Replies !
Eliminating Duplicate Rows
Hi, I have a table with four columns. like id,lastname, firstname,acctname. I have duplicate values for the three columns other than id column. like ID FirstNameLastname Acctname 1 john hopkins jh 2 john hopkins Jh 3 david webb dw 4 david webb dw 5 david webb dw 6 Dan Kennedy DK I want to eliminate the duplicate rows. id can be any one of them. Can any one suggest me with a query by which i can do this. Thanks in advance Mohan
View Replies !
Delete Duplicate Rows
I have a table which looks as follow: field1 field2 field3 field4 field5 ...... A B C A X ...... A B C B Y ...... A B C C Z ...... A B C A Y ...... . . . . . ...... I want to delete all the rows except one row. Anybody can help? Thank you very much.
View Replies !
Can We Somehow Mark Duplicate Rows ?
Hi, I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique. I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc.... Can anyone suggest 'any' possible way of implementing it ? Thanx Nisha
View Replies !
Creating (almost) Duplicate Rows
Hi everyone, I'm migrating some information for a client at the moment. They had everything in Excel files and I'm getting them into SQL Server. There are some differences in the way I am storing data and the way they were storing data. For each client they stored, they had something like Rel1 Rel2 Rel3 100 101 102 Now, what I have is a seperate row for each of Rel1, Rel2 and Rel3 so I would have 3 seperate rows with identical information except for Rel1. So I would have: Rel1 100 101 102 So one way I thought of doing it was inserting a new row specifying that the value for Rel2 should be stored in Rel1 and for the next row that the value for Rel3 should be stored in Rel1. Now, I am able to do this but SQL Server inserts an extra row will the NULL value in Rel1. Does anyone know why this would be happening? I think what it is doing is finding a NULL value in Rel3 after creating the two extra rows and is inserting that NULL. So I think I need to check for NULLs and not allow it to create a new row if, say, Rel3 is NULL. Any pointers are gladly welcome. (I know it's complicated )
View Replies !
Need To Get Rid Of Duplicate Rows In A Query
Hello I am fairly new to SQL and having spent much time over the manual I decided to ask for help. So here's my deal. I've got a query with 5 tables that I join together Code: SELECT * FROM Map INNER JOIN ThreatCategory INNER JOIN Threat ON ThreatCategory.threatCategoryID = Threat.threatCategoryID INNER JOIN Threat_Map ON Threat.threatID = Threat_Map.threatID ON Map.mapID = Threat_Map.mapID LEFT JOIN person on map.contentPersonID = person.personID WHERE (((DATEDIFF(dd, Map.dataAcquisitionDate, GETDATE()) > map.goodForDays) and (map.expired = '1')) or (map.expired = '3')) The problem is the table Threat_Map is a many to many mapping between the Map table and the Threat table. Eg) A map can have more than one threat and a threat can have more than one map. I know this is not the best way to have a database set up but its out of my hands as to changing the database. What I need help with is this. My application checks as to whether a certain field in the Map table is expired or out of date (as in the query). If so it gets some required information from the other tables using those joins. However, I don't want to get information for the same Map.mapID that's expired twice. I don't really care which ThreatID I get from the Threat_Map table I just need to get one of them to meet the objects standards. However, so far this seemingly simple task has eluded me. I'd like to do this in SQL. Is there perhaps a way to do this. If not I guess I'll just take care of it in the application. -Alex
View Replies !
Duplicate Rows In 1 Csv File
Hi, I am trying to import data from a csv files to a OLE DB Destination. The csv files contains all transactional changes . For example for a particular record the firstname, lastname, email address records change within the same csv file. I need to save only the last updated record from the csv files. I have tried "slowly changing dimensions" but these dont work when there is duplictes within the same csv file. Also have tried 'Sort' but this only stores the first occurance. Any ideas how i can store the latest changed data within 1 csv file.
View Replies !
How I Do To Eliminate Duplicate Rows?
Hello, I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure: Id_type, number_type, date, diagnosis, sex, age, city After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear. How I can do it?? Thank you very much. Regards, Angela
View Replies !
Del. Duplicate Rows From A Table Having No Pk
hi, create TABLE #t ( id int,names varchar(50)) INSERT INTO #t VALUES(1,'master') INSERT INTO #t VALUES(2,'tempdb') INSERT INTO #t VALUES(2,'tempdb') INSERT INTO #t VALUES(3,'model') INSERT INTO #t VALUES(3,'model') INSERT INTO #t VALUES(3,'model') INSERT INTO #t VALUES(1,'master') SELECT * FROM #t Iam using sql server 2005. I want to delete the duplicate records from the table.Please specify a method which is very good at performance. Thanks in Advance..........
View Replies !
Get Duplicate Value Rows - Complicate
Hi, I need some help to solve the following query. Consider the following table NAME OTHERNAME --------------------- APPLE-----AA APPLE-----AA APPLE-----AA ORANGE-----OO ORANGE-----O1 GRAPE-----G1 GRAPE-----G1 GRAPE-----G2 GRAPE-----G3 MANGO-----M1 MANGO-----M2 MANGO-----M3 OUTPUT should be ---------------- ORANGE-----OO ORANGE-----O1 GRAPE-----G1 GRAPE-----G2 GRAPE-----G3 MANGO-----M1 MANGO-----M2 MANGO-----M3 Advance Thanks for your help. Sri
View Replies !
|
TRACKER
