Help Eliminate Dupes
Nov 30, 2006
I am VERY new to SQL and I am having a heck of a time biulding a script to find and remove duplicate entries.
Here is the table structure.
CREATE TABLE [dbo].[SecurityEvents](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone.
select distinct * from dbo.SecurityEventsTest where recordnumber IN
(select recordnumber from dbo.SecurityEvents)
order by recordnumber
Could someone help??
Thank You,
John Fuhrman
http://www.titangs.com
View 9 Replies
ADVERTISEMENT
Feb 5, 2000
Greetings!
I have a database with several million records, I have found dupes and I need to get rid of them while keeping the original data in the db, kind of like delete all but 1. Any ideas of an easy way to do this?
Thanks,
Jimmy Ipock, MCSE, MCP+I
View 1 Replies
View Related
Dec 9, 2001
I have 3 tables, and im doing a join like:
select top 10 thits.fhits as hits, tmain.fheadline as rubrik, tmain.fpubfile as pub
from thits
join tmain on tmain.postid=tHits.postid
join tkeyscat on tkeyscat.postid=tmain.postid
where tkeyscat.fkeycat=60 order by hits desc
Which works great (almost).
The problem is when an article in tmain is cathegorized in more then
one cathegory, so the join tkeyscat on tkeyscat.postid=tmain.postid
might join in more then one result.
Im trying to select the 10 most read articles from tmain/thits where the article is in keycat 60.
How can I solve this?
tia
/frax
View 3 Replies
View Related
Apr 6, 2001
Hi ,
i need to delete duplicate rows in a table, i want a good logic and example to solve this issue. Please help me in this..
urs
vj
View 2 Replies
View Related
Feb 7, 2005
I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.
I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];
but it doesn't work since it's keeping the subset of the dupe row(s).
Can someone shed some light?
TIA
View 14 Replies
View Related
Oct 13, 2005
Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode
In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.
Any help or guidance that anybody can give will be most appreciated.
Dale
View 2 Replies
View Related
Apr 22, 1999
The Transaction Log which I'm working is so loaded. I trucated it, but isn't enough, is still so big.
What can i do for clean the log and have free space again?
View 3 Replies
View Related
Jul 29, 2003
Table A
JobNoClaimShipType
A1100I
A1200II
Table B
JobNoCost
A150
A1100
Result Expected
JobNOCost Claim Shiptype
A150 100I
A1100 200II
Hi all,
i've given a table structure with data
and the expected result .
I want to establish it in SQL server (7.0)
If i establish the inner join i get 4 rows (2*2)
Please let me know how to get the result
Thanx in adv
Tarriq
View 1 Replies
View Related
Jul 31, 2007
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
View 14 Replies
View Related
Mar 12, 2008
Hi,
In my query where clause I am using between to get data. Because of time in the data I need to eliminate that and compare, how can I eliminate. my where clause is as below. due to that my query performance is falling down. please help.
CONVERT(DATETIME, CONVERT(CHAR(20), OpportunityDate,110)) BETWEEN CONVERT(CHAR(20),@FDate,110) AND CONVERT(CHAR(20),@TDate,110)
Thanks
Sreenu
View 7 Replies
View Related
Mar 17, 2008
Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,
for ex., i have given an example.,
create table Example (ID int, Name varchar(30))
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')
select * from Example.,
i will get.,
ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush
.....
but i need like
ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush
I dont want repeated ID., How can i do that.,
View 14 Replies
View Related
Feb 8, 2008
'My table' is below with double row
lot value date
2 300 3/2/06
3 200 6/5/05
4 100 5/21/07
5 340 6/23/06
2 250 4/3/06
My query such as
SELECT lot, value, date
FROM my table
How can I eliminate 1 row of lot 2 and chose the recent date only?
Thanks for your help
Daniel
View 3 Replies
View Related
Mar 26, 2007
Hi All,
I have a SP, which i run inside a for loop.
I am running the SP for all the products in a listbox.
So for each product i am having the feature extracted through the SP
But some features are the same for 2, 3 products.
So in the datatable, i am getting the featrues repeated.
IS there any way to eliminate the duplicates from datatable, from server side?
Hope i am not confusing.
Eg: product1 --- test1, test2, test3
product2 --- test2, test4
so the datatable has -- test1, test2, test3, test2, test4
-- i have to eliminate one test2 from this.
Any ideas???
Thanks
View 1 Replies
View Related
Mar 14, 2003
I'm trying to run a set of DBCC commands to empty and then delete a secondary log file; however, no matter how large I make the primary log file it won't empty the secondary file. Any suggestions?
DBCC SHRINKDATABASE (VE, 10)
GO
alter database VE modify file (name= VE_log,size = 1200)
GO
dbcc shrinkfile (VE_log2,EMPTYFILE)
GO
Cannot shrink log file 3 (VE_Log2) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
17 3 86848 128 86848 128
--alter database VE remove file VE_log2
--GO
View 1 Replies
View Related
Jul 23, 2007
Hey,
I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them.
When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..
Thx in advance..
View 4 Replies
View Related
Apr 7, 2008
Hai
I have written one select statement and it returns the following records.
SELECT a.Name, b.address FROM tbl1 a INNER JOIN tbl2 b ON a.id=b.id
Name address
AAA XYZ
BBB
CCC
DDD
My requirement is to display only if address fields contain values.
For Eg. I want to display AAA XYZ record only. How can I write a query.
Help me.
Kamal..
View 3 Replies
View Related
Nov 20, 2006
hi, i have null values in my table , i want to eliminate the null values.
ie
this is my query
select p_type from process_general
output:
1.BSB HEATER PACKAGE
2.
so in my output one data and one null field is there. so i want to show output with out that null field, becos i am filling this datas in my combobox.so i need with out null field.please give me query for this,pleaseeeeeeeeeeeee
View 3 Replies
View Related
Jun 22, 2007
Hey Again,
I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:
SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID
FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID
This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).
Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
View 1 Replies
View Related
Apr 11, 2007
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 7 Replies
View Related
Sep 20, 2006
I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?
View 5 Replies
View Related
Oct 5, 2001
Hi
I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.
Thanks
Regards
-Leong
View 2 Replies
View Related
Mar 1, 2007
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
View 2 Replies
View Related
Apr 3, 2008
Hi Guyz
say i have a table
10011 NULL NULL Classical NULL
10011 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10005 NULL NULL Classical NULL
i want to eliminate the duplicate records and atable should look like
10011 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10005 NULL NULL Classical NULL
do we have any simple sql to do it or something complex.
thanks in advance !
View 6 Replies
View Related
May 1, 2015
We have rolap based cube .. when we run the report on that it is take more time than expected.I have run the profiler and identified .. same query is duplicated and running multiple times. I have run the query .. it took 10 seconds..how to eliminate duplicate queries .
View 3 Replies
View Related
May 30, 2007
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join
on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe
row for the person and the different categories he belongs to can come up in single field as comma sepatated string
Now the results are like this:
firstname lastname adress category
abc xyz aaa a
abc xyz aaa b
abc xyz aaa c
I want like below:
abc xyz aaa a,b,c
I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
View 7 Replies
View Related
Jun 26, 2007
HI
I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like
select ISNULL(dbo.tblperson.Email1, N'') + ';' + ISNULL(dbo.tblperson.Email2, N'') + ';' + ISNULL(dbo.tblperson.Email3, N'') AS Email from tablename.
One eg of the output of the above query when email2,email3 are having null values in the table is :
jacky_foo@mfa.gov.sg;;
means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.
Please let me know how can i do this
View 6 Replies
View Related
Feb 21, 2014
With this qry i need to only show in the result for ordate the date and not the time stamp. below is my qry and the first line of the results:
SELECT DISTINCT
adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust,
IDST_EVENT_ORDERS.ORDATE,
IDST_EVENT_ORDERS.ORTIME,
IDST_EVENT_ORDERS.EVWMIN,
IDST_EVENT_ORDERS.EVTDES,
[Code] ....
result for the field trying to change
ORDATE
1/1/2014 12:00:00 AM
I Only want to see the date.
View 6 Replies
View Related
Oct 24, 2005
I have a table with 3 fields. when I type
select * from test -- I am getting the results as below.
NAME AGE DEPT
AAA 23 AOD
BBB 27 NULL
CCC NULL NULL
DDD 23 POD
DEPT,AGE are displayed with "NULL" WHEN THERE IS NO value for that field . How can I eliminate this. I need space instead of NULL. When I export to text file there also contains NULL. Let me know how can I eliminate this.
Thanks in advance
View 13 Replies
View Related
Jun 30, 2015
I have the following query.
SELECT a.Line_Number as Line_Number,
Cast(a.election_effective_date as DATE) as election_effective_date,
Cast(a.Plan_Year_Effective as DATE) as Plan_year_Effective
FROM (SELECT aa.* FROM TEMP_Validation aa WHERE IsDate(aa.Plan_Year_Effective) = 1 AND IsDate(aa.election_effective_Date) = 1) a
Where Cast(a.election_effective_date as Date) Not Between Cast(a.plan_year_effective as Date)
and DATEADD(DD,-1,DATEADD(YY,1,Cast(a.plan_year_effective as Date)))
There is a malformed date in the Temp data, at line 4932. This is why I created a subquery that checks if the plan year and election effective dates are dates using the "IsDate" function. However, when I execute this I get the error "Conversion failed when converting date and/or time from character string". If I eliminate line 4932 the error no longer occurs. Somehow line 4932 is not being eliminated in the subquery, except if I run JUST the subquery it will not include line 4932. What am I doing wrong on these queries and how can I correctly get the subquery to eliminate the malformed date?
View 5 Replies
View Related
Sep 13, 2007
I am trying to access a report via url
http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2
it always asks for username and password.
All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.
can you please help, how to override this login screen.
Thank you very much for the information.
View 3 Replies
View Related
Feb 28, 2008
In my employee table has the following fields empid, empFname, empLname, email, city
Say it has data like follows:
1, Lucy, Sam, l@some.com, city1
2. Sam, Wite, l@some.com, city2
3. Laura, Mac, l@some.com, city2
4. Stacy, Soo, s@no.com , city1
So in my case I want to show all the column but I want to eliminate multiple email addresses. I tried Distinct but its not workin because here every column is not distinct. So what should I use?
In my case I only want to show empID 1, 3, 4. I want to show all the columns
View 5 Replies
View Related
Jul 25, 2001
I am getting a data loaded in a DB table to a character field padded from the right with hexadecimal zeros 0x00. How to get rid of it?
Thank you.
View 3 Replies
View Related
Jul 10, 2015
I've been trying to make the following query more performant by breaking it up into smaller pieces.
SELECT MT.A3+MT.A4 AS A34,MT.A3
-- ,M.*
FROM
Master_TAB M
JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum
[Code] ....
I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB
Here's my attempt...
-- MASTER_TAB has 1.7 million rows and 50 columns
CREATE TABLE [dbo].[tmp_ABC](
[A1] [varchar](13) NOT NULL,
[A2] [varchar](5) NOT NULL,
[A3] [varchar](4) NOT NULL,
[A4] [varchar](4) NOT NULL,
[A5] [int] NULL
) ON [PRIMARY]
[Code] ...
This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)
Select c.A3+c.A4 as A34, c.A3, c.A1
-- M.*
from tmp_DEF c
join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2
order by c.A3, C.A4
if I just run the following I get no spill:
Select c.A3+c.A4 as A34, c.A3, c.A1
from tmp_DEF c
order by c.A3, C.A4
as soon as I add the Master table as a Join I get the Spill...
I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?
View 9 Replies
View Related
