Hi, All
I have a problem with one table.
This table is corrupted so I drop the table and recreate the table..(Of course I export data) After that I try to put the primary key to new table but it won't allow me to do it. Error message says " There are duplicated key existed" Therefore I open up the EM and take a look at that table. There is key in that table but not Primary key..(also from the query analyzer using sp_help)
My question to U is how can I find this duplicated key and delete that info? I think somewhere in the system table contains this info but I don't know where:-(((
Hello, I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6….
I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=’minimum’ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?
I have a column of primary key, integer and identity. It has been working fine til 2 days ago. Though the column is IDENTITY, it duplicated number by triple! How's that possible?
Has anyone came across this problem? If you have, how did you fix it?
Hello, I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6€¦.
I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=€™minimum€™ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?
I have a table that I truncate with an execute sql task, which uses the following statement: truncate table dbo.tablename.
Afterwards I fill the table with data. Sometimes it gives an error that there are duplicated keys in the table, but it's truncated before the fill... Is there anyone who has an idea on this matter?
I have just started developing in SQL Express in the last 2 months so still learning. The problem I€™m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I€™m asking.
A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.
ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS
SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],
CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],
MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'
WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'
WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'
WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'
WHEN DATEPART(MM,SessionDate)=5 THEN 'May'
WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'
WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'
WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'
WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'
WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'
WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'
WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'
I have many data in a table in which some rows are duplicated. How can I, for all duplicated rows, delete the extra rows and leave only one? You may assume checking one column is enough to tell if a row is duplicated.
I have a unique account number for every record in the table (hence the p-key) however I have route numbers that are duplicated in the table. How do I find all records that have a ROUTE_NUMBER duplicated.
In other words, how do I query records where the route number occurs more than one time in the table.
I would like the following results....
ACCOUNT NUMBER ROUTE NUMBER NAME STATE 12345 1 WMT NY 48734 1 CBS TX 3945857 1 NBC LA
I've got a trouble with my query and left outer joins. I've got 2 tables, table A and B, both have the same record called ID. I used this query: SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID
This is OK and works fine, my trouble comes when i have a duplicated ID on both A and B. Instead of return 2 fields, it returns me 4.
Is there some way to force SQl server to return only the first founded on B, but the 2 duplicated IDs on the A table?
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
Hi, I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................
I am currently working to solve this problem that i am facing. I just cant' get my desired solution to my problem. As per below is the current db view. emp_id skill level years remarks 2541COMPUTERBAIK1<null> 2541WORD PROCESSINGBAIK1<null> 2541EXCEL BAIK1<null> 2541POWERPOINTBAIK1<null>
how do i get this repeated emp_id to have a view of..
emp_id skill level 2541COMPUTER.wordprocessin,excel,powerpointBAIK,baik,baik,baik years remarks 1,1,1,1 <null>,<null>,<null>,<null>
i just can't seem to get this comin up.. please kind advise thanks all!
ColumnA ColumnB ColumnC ------------------------------- Alice Lukas Alice.Lucas James Redford James.Redford James Redford James.Redford Michael Jackson Michael.Jackson John Brown John.Brown John Brown John.Brown John Brown John.Brown George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas James Redford James.Redford James Redford James.Redford1 Michael Jackson Michael.Jackson John Brown John.Brown John Brown John.Brown1 John Brown John.Brown2 George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?
Public Sub ProductShow()
Dim myReader As SqlCeDataReader Dim mySqlCommand As SqlCeCommand Dim myCommandBehavior As New CommandBehavior Try connLocal.Open() mySqlCommand = New SqlCeCommand mySqlCommand = connLocal.CreateCommand mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id " myCommandBehavior = CommandBehavior.CloseConnection myReader = mySqlCommand.ExecuteReader(myCommandBehavior) While (myReader.Read()) cboProductPurchased.Items.Add(myReader("P_Name").ToString()) End While myReader.Close() Catch ex As Exception MsgBox(ex.ToString) Finally connLocal.Close() End Try
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
Hi,I've got a db table containing 5 columns(excluding id) consisting of1.) First Half of a UK postcode2.) Town name to which postcode belongs3.) Latitude of Postcode4.) Longitude of Postcode5.) Second Part of the PostcodeI want to select columns 1,2,3 and 4, but once only. There are oftenseveral entries where 1 and 2 are the same but 3 and 4 are differenti.e.WA1Bewsey and Whitecross53.386492-2.596847WA1Bewsey and Whitecross53.388203-2.590961WA1Bewsey and Whitecross53.388875-2.598504WA1Fairfield and Howley53.388455-2.581701WA1Fairfield and Howley53.396117-2.571789My current query isSELECT DISTINCT Postcode, Town, latitude, longitudeFROM PostcodeWHERE Postcode.Postcode = 'wa1'ORDER BY Postcode, TownHowever as latitude and longitude differ on each line DISTINCT doesnot do what I'm looking for.Can anybody suggest a way changing the query to just give the firstinstance of each Postcode/Town combo?I.E.WA1Bewsey and Whitecross53.386492-2.596847WA1Fairfield and Howley53.388455-2.581701Many thanks!Drew
This one's kind of hard to explain, so I've opted to post a simplifiedversion of our view that prompted me to ask this question: Thequestion is re-asked after the view...create view MainView (PrimaryKeyID,SubTotal1,SubTotal2,GrandTotal)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total),sum(t1.Total) + sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...Notice in the 3rd column called "GrandTotal" how it calls the function"sum" two more times. Common sense tells me that this is notnecessary. in our case it's orders of magnitude worse... Is the queryoptimizer smart enough to only call these sums once per row in"SomeTable"? Common sense tells me that if we were to break the viewsapart into two views it would avoid this ineffeciency:create view InnerView (PrimaryKeyID,SubTotal1,SubTotal2)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...create view OuterView (PrimaryKeyID,SubTotal1,SutTotal2,GrandTotal)asselect iv.PrimaryKeyID,iv.Total1,iv.Total2,iv.Total1 + iv.Total2from InnerViewNotice how it appears that we've tricked the optimizer into thinkingthere are less operations. So my question is how does views handlethis situation? Does the optimizer treat both version the same? Or isone faster than the other? Or is there another, faster way? Doesadding levels of views slow down things, or are views simply likemacros and get removed when compiled (I think I've read the latter istrue actually)Thanks,Dave
SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @SOLUTIONID ]
With the following statement:
SELECT [VERSIONID], [SOLUTIONID], [BASEVERSIONID], [VERSIONNUMBER], [NAME], [DESCRIPTION], [CREATEDATE], [UPDATEDATE], [VERSIONCLOSED] FROM VERSIONS WHERE SOLUTIONID = @SOLUTIONID AND VERSIONCLOSED = 1 AND VERSIONID IN (SELECT MAX(VERSIONID) FROM VERSIONS WHERE SOLUTIONID = @SOLUTIONID);
Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?
Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @@IDENTITY won't cut it.
A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.
I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012.
FS1 contains huge number of files due to which F drive is completely full.
So, I am trying to move some of the extra files from one FileStream (FS1 on F drive) to another FileStreams (FS2 on H drive and FS3 on E drive) using command:
dbcc shrinkfile('FS1', emptyfile)
Then, I take the Full and Differential backup of the database and issue the CheckPoint and try to delete the already duplicated files from the Filestream FS1 to get some space in the F drive using command:
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD FROM BF_ORGN A INNER JOIN BF_ORGN_CNSL_TBL B ON A.CD=B.BF_ORGN_CD WHERE A.BF_ACTY_CD IS NOT NULL ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD AC_21234_2 AC_21200_1 402 AC_21236_2 AC_21200_1 402 AC_21238_2 AC_21200_1 402 AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD) AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
SELECT R.name, R.age,R.DOB, ISNULL(D.Doc1,'NA') AS doc1, ISNULL(C.Doc2,'NA') AS doc2 FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id inner join RES1 D ON D.Response_Id=S.Response_Id inner join REQ1 C ON C.Request_Id=R.Request_Id
select * from RES1 where Response_Id = 111 -- return 3 select * from REQ1 where Request_Id = 222 --- returns 2
So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...
Hi.I have a table (websitehits) which holds statistics about websites.This table has a date field (datecounted). What I need is to create aquery which returns a list of dates between two date ranges (say ayear ago from today and a year from now) which only shows dates thathaven't been used in the websitehits table for a given website.For example if my table contains something like:Website Datecounted HitsSite101/01/046000Site101/02/046500Site101/03/046250Site201/03/041000Site201/04/041200Site201/05/041500So if query for ‘site1' then I'd get a list of all dates between30/11/03 to 30/11/05 with the exception of the dates 01/01/04,01/02/04 and 01/03/04.So far I've tried to do this using another table named calendar whichcontains a very long list of dates and to use this to produce the list– but I'm not getting very far.By the way I'm using sql server, an I need this query to generate alist for an asp page - so I need to pass the website name as aparameter so I guess I need to make this query as a stored procedure.Does anyone know how this can be done?
I've a matrix report with two row groups. One of the row groups has been used to do alternate coloring in report rows. The issue is that the header text that I've provided for the row group repeats twice. Another interesting point to note here would be this doesn't happen in the report preview but only when the report is displayed through a report viewer in an ASP.Net application. I can guess that it must have got something to do with the invisible row group but can't get my head around on how to avoid it?
The report currently looks something like below:
Month 2007 2008
Month Total Ave % Total Ave % ----------------------------------------------------------------------- January 12 6 1.5......................etc etc.
I want the text "Month" to come only once. Thanks.
Is there any way to hide duplicated rows in SSRS except this formula iif(Fields!YourField.Value = Previous(Fields!YourField.Value), True, False)Â because doesn't work here.
I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO. FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT. HOW THE STATEMENT SHOULD BE?
