I have some duplicate records in my table, I need to make those records so they are not duplicate. There is a small dateTime field in that table, I want to add seconds to each row of that field in sequential order so that the record becomes distinct so for e.g if I have this in the field
I am not sure if it is possible, but if it is then how can I achieve this. There are around 20 columns in this table or if there is any other way to make these record distinct.
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
Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row. Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin', leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType', SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption', leg_depart_time_local AS 'DeptTime', leg_arrive_time_local AS 'ArrTime', dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays', TruckEditor.EffectiveDays as 'NewEffectiveDays' FROM lhif_prod JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd AND Type_Code = 'T' AND leg_orig = @LocID ORDER BY RteNum, Dest, DeptTime Here is what comes back from this query:ABE00 ABEA ABER CTV5 H 1855 1915 MTWT--- NULLABE01 ABEA ABER CTV5 H 1941 2001 MTWT--- NULLABE02 ABEA ABER CTV5 H 2045 2105 MTWTF-- NULLABE03 ABEA ABER CTV5 H 2059 2119 MTWTF-- NULLABE04 ABEA ABER CTV2.5 H 2245 2305 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- MT-TF--ABE12 ABEA ABER WALKIN H 2109 2124 MTWTF-- NULLEF038 ABEA EWRHB 53BULK H 0100 0245 -TWTFS- NULLEF085 ABEA EWRHA CTV5 H 1955 2140 MTWT--- NULLEF106 ABEA EWRHB CTV5 H 1901 2046 -----S- NULLEF140 ABEA ABER CTV5 H 0550 0610 M------ NULLEF166 ABEA EWRRA CTV5 H 2230 0010 MTWT--- NULLEF366 ABEA EWRRA CTV5 H 2230 0010 ----F-- NULLEF543 ABEA EWRRA CTV5 H 2200 2345 MTWTF-- NULL The 2 rows in bold are the issue right now. There should only be 1 row (the 2nd one where the last column is not null). I'm not sure why it returns both columns when I'm doing a join on there to add that last column. Can anyone help me out with this? I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide. Thanks.
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
How do I get distinct TitleID and Titles? Right now I'm still getting duplicates on them both. Here's my stored procedure. select Distinct (Titles.Titleid), Titles.Title as TITLE, classifications.[description]as TOPIC,Titles.descriptions,media.[description] as MEDIA from Titlesjoin resources on resources.Titleid = Titles.Titleidjoin media on media.mediaid = resources.mediaidjoin titleclassification on titleclassification.titleid = titles.titleidjoin classifications on classifications.classificationid = titleclassification.classificationid WHERE Title LIKE 'p' + '%' GROUP BY Titles.titleid, titles.title,classifications.[description],Titles.[descriptions],media.[description] Thanks!
hi, i want to ask a simple question, i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews). the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'?? thanks..
Hi, I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement: "SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;" and select only unique "chName's" records
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel
TableName: Order_Archive Fields: orderid load_date filename order_date dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
I have a table contains information related to sales:
SO number Order Date Customer SellingPerson 1001 2012/07/02 ABC Andy 1002 2012/07/02 XYZ Alan 1003 2012/07/02 EFG Almelia 1004 2012/07/02 ABC John 1005 2012/07/02 XYZ Oliver 1006 2012/07/02 HIJ Dorthy 1007 2012/07/02 KLM Andy 1008 2012/07/02 NOP Rowan 1009 2012/07/02 QRS David 1010 2012/07/02 ABC Joey
Now, i want to write a query using CTE that gives me first five distinct customer in result set:
SO number Order Date Customer SellingPerson 1001 2012/07/02 ABC Andy 1002 2012/07/02 XYZ Alan 1003 2012/07/02 EFG Almelia 1006 2012/07/02 HIJ Dorthy 1007 2012/07/02 KLM Andy
I wrote this query :
With t(so_number,order date,customer, SellingPerson) as (select top 5 so_number,order date,customer, SellingPerson from t) select distinct billingcontactperson from t order by so_id
And getting this error:
Msg 252, Level 16, State 1, Line 1 Recursive common table expression 't' does not contain a top-level UNION ALL operator.
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely: ID, Date, Program
I need to extract all the distinct Program's from the table. I have don so with: Select distinct Program from table This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour Apple Y Green Orange N Orange Banana Y Yellow Grape Y Green Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour Apple Y Green Orange N Orange Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Yes, I know this subject has been exhausted, but I need help in locating the discussion which took place a few months ago. Sharon relayed to the group a piece of software (expensive) which would help in my particular situation. I grabbed a demo and have gotten the approval for purchase. Unfortunately, I don't have the thread with me at work.
The problem:
Number Fname Lname Age ID 123 John Franklin 43 1 123 Jane Franklin 40 2 123 Jeff Franklin 12 3 124 Jean Simmons 39 4 125 Gary Bender 37 5 126 Fred Johnson 29 6 126 Fred Johnson 39 7 127 Gene Simmons 47 8
The idea would be to get only unique records from the Number column. I don't care about which information I grab from the other columns, but I must have those fields included. If my resultant result set looked as follows, that would be fine. Or any other way, as long as all of the fields had information and there were only unique values in the Number field.
Number Fname Lname Age ID 123 Jeff Franklin 12 3 124 Jean Simmons 39 4 125 Gary Bender 37 5 126 Fred Johnson 39 7 127 Gene Simmons 47 8
If anyone remembers this discussion, mainly the date, I would really appreciate it.
I have two tables, one contains all work orders, the second contains records on work orders that are linked to customoer orders. I'm trying to create a query that will return specific fields from the table that contains orders in the linked order table, and only the work orders in the all order table that (work_order) do not exist in the linked order table (demand_supply_link). I have tried several queries and cannot get the results I desire. Here is the query I am currently trying.
SELECT DISTINCT WORK_ORDER.DESIRED_WANT_DATE as 'Want Date', DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID as 'WO Id', WORK_ORDER.DESIRED_QTY as 'End Qty', DEMAND_SUPPLY_LINK.SUPPLY_PART_ID as 'Part Id', CUST_ORDER_LINE.CUSTOMER_PART_ID as 'Cust Part', OPERATION.RESOURCE_ID as Resource, PART.DESCRIPTION as Description, CUSTOMER.NAME as Name FROM ((((DEMAND_SUPPLY_LINK INNER JOIN CUST_ORDER_LINE ON DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID) INNER JOIN WORK_ORDER ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = WORK_ORDER.BASE_ID) INNER JOIN OPERATION ON WORK_ORDER.BASE_ID = OPERATION.WORKORDER_BASE_ID) INNER JOIN PART ON WORK_ORDER.PART_ID = PART.ID) INNER JOIN (CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID) ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID WHERE WORK_ORDER.DESIRED_WANT_DATE Is Not Null AND OPERATION.RESOURCE_ID in ('ASSY','FAB 1','PLAY TRK') AND WORK_ORDER.STATUS='R'
UNION SELECT distinct work_order.desired_want_date as 'Want Date', work_order.BASE_id as 'WO Id', work_order.desired_qty as 'End Qty', work_order.part_id as 'Part Id', operation.resource_id as Resource, part.description as Description FROM WORK_ORDER INNER JOIN PART ON PART_ID=WORK_ORDER.PART_ID INNER JOIN OPERATION ON WORK_ORDER.BASE_ID=OPERATION.WORKORDER_BASE_ID WHERE WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL AND OPERATION.RESOURCE_ID IN ('ASSY','FAB 1', 'PLAY TRK') AND WORK_ORDER.STATUS='R'
This is the error I receive: Server: Msg 205, Level 16, State 1, Line 1 All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.
The all orders table (work_order) will not have the other fields to link to as there is no customer order linked to them.
Can someone tell me the best procedure when trying to find duplicate records within a table(s)?
I'm new using SQL server and I have been informed that there maybe some DUPS within unknown tables. I need to find these DUPS.
If someone can tell me how to perform this procedure I would apprciate it. And if you reply can also include examples that i could follow for my records.
Table1 has shop# and shop_id. Every shop# should have only one shop_ID. There has been a few data entry errors where a shop# has duplicate a shop_id. How to write a query for shop#s that have more than one shop_id?
Not so sure how simple this question is but here is what happened. I installed SQL Server 2005 on a new Win Server 2003. I exported the tables and their data from the old machine to the newly established database on the new machine.
It looks like all my records were duplicated. When I try to delete one of the duplicates it won't work because both rows are effected. I can't set my primary key now and if I try to create a new database with the primary key already set than the import fails.
Any one run into this before or know what's going on?
