Removing Duplicates From Inner Joins
Sep 27, 2004
Hi There,
I was wondering if someone could help me with the results on this query, at the moment I am getting values repeated and I was wondering if it was possible to have some of the columns grouped, I have tried to have grouping at the end of the query but this still did not group the rows.
Thanks in advance for your answer - Sean
The structure that i'm trying to acheive is like the following:
with each colour having multiple quantitys for each size:
colourdesc| sizedesc | xs | s | m | l
-----------
black |qoh| | 0 | 2 | 0 | 7
-----------
white |qoh| | 0 | 0 | 0 | 0
-----------
!-- query results
+------------+------+------+-----------+--------+---------------+----------+
| qoh | size | shop | style | colour | colourdesc | sizedesc |
+------------+------+------+-----------+--------+---------------+----------+
| 0000000007 | 6 | 1 | 10475RIB1 | 2 | BLACK | L |
| 0000000000 | 6 | 1 | 10475RIB1 | 36 | CORRECTOR6771 | L |
| 0000000000 | 6 | 1 | 10475RIB1 | 15 | SPEARMINT6581 | L |
| 0000000000 | 6 | 1 | 10475RIB1 | 6 | WHITE | L |
| 0000000007 | 5 | 1 | 10475RIB1 | 2 | BLACK | M |
| 0000000000 | 5 | 1 | 10475RIB1 | 36 | CORRECTOR6771 | M |
| 0000000000 | 5 | 1 | 10475RIB1 | 18 | MERINGUE4016 | M |
| 0000000000 | 5 | 1 | 10475RIB1 | 6 | WHITE | M |
| 0000000002 | 4 | 1 | 10475RIB1 | 2 | BLACK | S |
+------------+------+------+-----------+--------+---------------+----------+
!--- query
select qoh
, shopsales.size, shopsales.shop, shopsales.style, shopsales.colour, co.colourdesc, sz.sizedesc
from shopsales
inner
join shops
on shopsales.shop = shops.shop
inner
join colours co
on shopsales.style = co.style
and shopsales.colour = co.colour
inner
join sizes sz
on shopsales.style = sz.style
and shopsales.size = sz.size
where shopsales.shop = 1
and shopsales.style = '10475RIB1'
View 1 Replies
ADVERTISEMENT
Aug 2, 2000
I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.
I need a query to do this.
Can anybody help.
-Rajesh
View 3 Replies
View Related
Jan 27, 2005
I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.
It doesnt matter which duplicate i remove as long as there are unique email addresses.
so here is the format of the table:
id
email
firstname
lastname
datebirth
if i do a:
SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer
it returns 21345
and
SELECT Count(Email)
FROM Customer
returns 28987
I can get the unique email addresses into another table by going:
SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer
but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?
Thanks in advance
Tom the SQL beginner
View 5 Replies
View Related
Jul 20, 2005
HiI have inherited a web app with the following table structure, and need toproduce a table without any duplicates. Email seems like the best uniqueidentifier - so only one of each e-mail address should be in the table.Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able toget a duplicate count working:select Email, count(*) as UserCountfrom dbo.Membersgroup by Emailhaving count(*) > 1order by UserCount descBut the methods for create a new table without duplicates fail. My code forthe 2nd method is:sp_rename 'Members', 'temp_Members'select distinct *into Membersfrom temp_MembersTable....CREATE TABLE [dbo].[Members] ([MemberID] [int] IDENTITY (1, 1) NOT NULL ,[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,[DateAdded] [smalldatetime] NOT NULL ,[SendMail] [smallint] NOT NULL) ON [PRIMARY]GOThanks B.
View 2 Replies
View Related
May 19, 2008
Using a sort transform to remove duplicates takes a very long time to execute.Is there a better way to remove duplicate information.
I have alot of duplicates within the data.
I would like to know a better way than using sort transform
Please let me know
View 2 Replies
View Related
Jan 12, 2006
I found a query on here to find all my duplicates (altered to fit mine)
----
select * from yourtable
where WCD_no in
( select WCD_no from yourtable
group by WCD_no having count(*) > 1 )
----
and it works great. However, when I try and delete one of these, I get the following error message.
"key column is insufficient or incorrect. Too may rows were affected by update"
Any idea on how I can delete these without having to drop the table or anything?
View 2 Replies
View Related
Mar 21, 2014
The database has Name,Email, and skill. Though the name is distinct it is repeated as it has different skills. I would like to remove duplicate names and add the corresponding skill to the only one row.
From the stored procedure, combining 3 tables I got the output as:
NameemaildepartmentSkill
ArunemailidTech teamTechnical
ArunemailidTech teamLeadership
ArunemailidTech teamDecision Making
BinayemailidMarketingTechnical
BinayemailidMarketingDecision Making
I would like to remove the duplicate Name fields and combine the Skill in a single row as other fields are same.
So the output should be
NameemaildepartmentSkill
ArunemailidTech teamTechnical, Leadership, Decision Making
BinayemailidMarketingTechnical,Decision Making
View 2 Replies
View Related
Jul 5, 2007
I have Data like below
TableA
Col1 Col2 Col3
1 'Test One' 4/7/2007
1 'Test One-1' 4/7/2007
2 'Test' 4/7/2007
3 'Test Three' 4/7/2007
3 'Test Three-1' 4/7/2007
3 'Test Three-2' 4/7/2007
I need to delete the duplicate records[Latest record has to be remaining like that] from above table. i need the out put like below
Col1 Col2 Col3
1 'Test One-1' 4/7/2007
2 'Test' 4/7/2007
3 'Test Three-2' 4/7/2007
If any body know the solution please help me.
Regards
Hanu
View 12 Replies
View Related
Jan 3, 2008
Hi, I'm in the midst of an Access 2003 to SQL server 2000 upsizing project and have come across a table on Sql Server that has a field that looks like it's supposed to be the PK but it contains duplicates. What I'd like to do is to have a cursor start at the first value and increment the next value by 1. Could someone explain how I'd go about this?
Many thanks,
Peter
View 6 Replies
View Related
May 18, 2012
I have a very large table that can contain up 3 to 5 duplicate records. Every month around 100,000 new records come in. Sometimes it's an ammended record, other times is just duplicated by error.
Is it possible to keep the latest record dumped into the table and delete the others? Does SQL track the order of the data being dropped into the table?
The layout would look like this. There are 10-15 other columns in the table where adjustments can also be made.
Lease# Year Month Production
12345 2008 10 1,231
12345 2008 10 1,250
12345 2008 10 1,250
View 2 Replies
View Related
Jun 4, 2014
I have table like this and I need to remove the duplicates selecting the Min Date
IDDate Alloc ProdWater Tub Cas
3752011-10-10 00:00:00.00000014.70
3752011-10-11 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3232014-04-26 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3562014-04-02 00:00:00.000000318.8471.1
3562014-04-03 00:00:00.000000318.8471.1
3562014-04-04 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
3232014-05-21 00:00:00.000000649.1976.9
3232014-05-22 00:00:00.000000649.1976.9
I would like my result set to be like this:
3752011-10-10 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
View 2 Replies
View Related
May 6, 2015
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
View 5 Replies
View Related
Mar 17, 2008
-- declared variables
declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100)
declare @list varchar(8000)
-- set values to variables
set @list = ''
set @database_name = 'data200802_dan'
set @table_name = 'other02'
set @primary_key_field = 'callid'
use database
select @list = @list + column_name + ', '
from information_schema.columns
where table_name = @table_name --table name
and column_name != @primary_key_field --unique identifier
select @list = substring(@list, 1, len(rtrim(@list)) - 1)
--above 5 lines btw came from a helper in the msdn forum. thanks
SELECT DISTINCT @list
INTO '#' + @table_name
FROM @table_name
@table_name + ':'
IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0
BEGIN
INSERT INTO
@database_name + '.dbo.' + @table_name + '(' + @list + ')'
SELECT
@list
FROM
'#' + @table_name
END
ELSE
BEGIN
DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')'
GOTO @table_name
END
DROP TABLE '#' + @table_name
the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.
is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..
this is for removing duplicates btw. thanks!
View 3 Replies
View Related
Dec 20, 2013
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber
group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id
Having sr.spec_checklist_id = 30 and count(*)>1
order by sr.linked_to_worknumber
Sample of query results:
idlinked_to_worknumberspec_checklist_id
18323IP-DN-03377430
24229IP-DN-03377430
18294IP-DN-03380830
28027IP-DN-03380830
18295IP-DN-03381030
19924IP-DN-03381030
In the 3 cases, I would want to retain only records with ids 24229,28027 and 19924.
View 9 Replies
View Related
Jun 2, 2015
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
View 4 Replies
View Related
May 15, 2015
I have a table with call data (ContactID, Queues Entered, Call Status, Date & Time Stamps etc). Each entry relating to a contact ID goes onto a new row. The first row for a contact is the date and time it is created. It then captures the queue (or queues) it enters before it is answered. Finally, it captures when the call is released (Completed).
I'm trying to link all this data into one single row per contact ID to make it easier to report on.
I started off by using DISTINCT to pull back all of the Contact ID's. I then used a Left Join to pull back the date and time of creation. I created a further Left Join to pull back the first queue that it entered and so on.
When I did this, I started getting duplicates. This is because some calls enter more than one queue.
How can I do this so that it only has one ContactID per row. Also, for the Queue, is there anything I can do to ensure it pulls back the first Queue it enters? (These are time stamped). Subsequently, I would then need to add the second and third queue it enters in other columns. (A call can enter a maximum of 3 queues).
View 8 Replies
View Related
Jan 26, 2015
I ran this query to populate a field with random numbers but it keeps populating with some duplicate records. how I can remove the duplicates?
UPDATE APRFIL
SET ALTATH = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);
Below are sample output that I need the dupes not show. The table already exist and its sql 2008
155957
155957
155968
155974
155976
15599
155990
155997
155997
156005
156008
View 2 Replies
View Related
May 23, 2014
I am building a application, which will generates a MASTER query with 15 fields & 5 tables joins.Now, the user selects only some fields from that master fields and generates the CHILD query. This is creating a performance issue.
To minimize this, i would like to remove the not necessary joins in the child query.
View 8 Replies
View Related
Jun 13, 2014
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I am getting same result set.
View 6 Replies
View Related
Oct 22, 2014
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
View 9 Replies
View Related
Jan 26, 2015
Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies
View Related
Nov 3, 2000
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
View 1 Replies
View Related
Aug 11, 2005
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies
View Related
Oct 12, 1999
Hi,
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
Angel
View 1 Replies
View Related
Feb 29, 2008
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
View 14 Replies
View Related
Sep 1, 2005
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
View 2 Replies
View Related
Mar 2, 2006
I need some help.
I have created a database that looks like the following:
FirstName Table link to Main Table.
I have created a Stored procedure that looks like this:
Create procedure dbo.StoredProcedure
(
@FirstName varchar(20)
)
Declare FirstNameID int
Insert Into Main Table
(
FirstName
)
Values
(
@FirstName
)
Select @FirstNameID = Scope_Identity()
How could I redesign this to check if a value exists and if it exists then simply use that value instead of creating a new duplicate value?
Thanks!!
View 2 Replies
View Related
Jun 27, 2001
I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.
anyway ive been trying to do it in SQL with no luck
here is a simplified version of my dilema
*****
NAMEEMAILAGE
tomtom@mail23
tombomb tom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
marycool@mail89
thomas tom@mail13
richrich@mail65
richdick@mail65
tomtom@mail23
tomtom@mail23
so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this:
*****
NAMEEMAILAGE
tomtom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
richrich@mail65
richdick@mail65
Can you think of a way to do this?
i tried the following but it gets rid of duplicates of other fields which is not what i want to do
CREATE table distinct_records
SELECT name, Email, Age
FROM duplicate_records
GROUP BY name, Email, Age;
any ideas guys?
thanks
tom.harrow@netpoll.net
View 1 Replies
View Related
Nov 7, 2005
i've imported a number of spreadsheets into a database
the schema is
directoryid firstname, lastname, extension
703168 Andrew Lim 78094
703154 Joseph Egan 78888
704548 Andrew Lim 78094
I realized the spreadsheet had a large number of duplicates. How can i delete the duplicates ?
like deleting directoryid 703168 only.
View 1 Replies
View Related
Nov 9, 2005
i'm trying to get duplicates out of the my database
SELECT COUNT(*) AS Amount,
Firstname,
surname,
Internalextension
FROM iac.dbo.sf_profil
GROUP BY FirstName, surname, internalextension
HAVING COUNT(*) > 1 order by firstname, surname
How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?
View 2 Replies
View Related
May 8, 2008
Hi,
This is the query which shows me the duplicates
Some of the records have more than one records
I would like to know how to delete the extra records so that I will end up with one record per row.
select
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last,
Count(*) as 'count'
from
tblPricesClean
group by
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last
having count(*) > 1
order by
count desc
View 4 Replies
View Related
Jun 13, 2008
Hi,
Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp
Thanks in Advance!
Sherri
View 4 Replies
View Related
May 10, 2006
Hi,
I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.
Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)
Thanks, sorry if this is too confusing. Jeff
View 14 Replies
View Related
