I need to eliminate Duplicates in my Sql Query, tried to use distinct and that doesn't seem to work, can anybody pls.help.
duplicates are in #ddtempC table, and am writing a query to get a country name from the hash table where hash table has duplicates
hash table contains (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME).
and trying to write condition on THEATER_CODE and COUNTRY_CODE to get Country_name
and THEATER_CODE AND COUNTRY_CODE HAS DUPLICATES. whenever i do a sub query i get the below error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT USER_FIRSTNAME, USER_LASTNAME,
user_countryCode,
USER_COUNTRY = (SELECT DISTINCT RTRIM(LTRIM(COUNTRY_NAME)) FROM #ddtempC WHERE RTRIM(LTRIM(COUNTRY_CODE)) = USER_COUNTRYCODE AND RTRIM(LTRIM(THEATER_CODE)) = USER_THEATERCODE)
FROM [user]
WHERE USER_USERNAME IS NOT NULL AND User_CreationDate BETWEEN '1/2/2007' AND '4/11/2008'
ORDER BY User_TheaterCode;
Have a pretty simple wuestion but the answer seems to be evading me:
Here's the DDL for the tables in question:
CREATE TABLE [dbo].[Office] ( [OfficeID] [int] IDENTITY (1, 1) NOT NULL , [ParentOfficeID] [int] NOT NULL , [WebSiteID] [int] NOT NULL , [IsDisplayOnWeb] [bit] NOT NULL , [IsDisplayOnAdmin] [bit] NOT NULL , [OfficeStatus] [char] (1) NOT NULL , [DisplayORD] [smallint] NOT NULL , [OfficeTYPE] [varchar] (10) NOT NULL , [OfficeNM] [varchar] (50) NOT NULL , [OfficeDisplayNM] [varchar] (50) NOT NULL , [OfficeADDR1] [varchar] (50) NOT NULL , [OfficeADDR2] [varchar] (50) NOT NULL , [OfficeCityNM] [varchar] (50) NOT NULL , [OfficeStateCD] [char] (2) NOT NULL , [OfficePostalCD] [varchar] (15) NOT NULL , [OfficeIMG] [varchar] (100) NOT NULL , [OfficeIMGPath] [varchar] (100) NOT NULL , [RegionID] [int] NOT NULL , [OfficeTourURL] [varchar] (255) NULL , [GeoAreaID] [int] NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL , [OfficeBrandedURL] [varchar] (255) NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[OfficeManagement] ( [OfficeID] [int] NOT NULL , [PersonnelID] [int] NOT NULL , [JobTitleID] [int] NOT NULL , [CreateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [SeqNBR] [int] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[OfficeMls] ( [OfficeID] [int] NOT NULL , [SourceID] [int] NOT NULL , [OfficeMlsNBR] [varchar] (20) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[Personnel] ( [PersonnelID] [int] IDENTITY (1, 1) NOT NULL , [PersonnelDisplayName] [varchar] (100) NOT NULL , [FirstNM] [varchar] (50) NOT NULL , [PreferredFirstNM] [varchar] (50) NOT NULL , [MiddleNM] [varchar] (50) NOT NULL , [LastNM] [varchar] (50) NOT NULL , [PersonalTaxID] [varchar] (9) NOT NULL , [HireDT] [datetime] NOT NULL , [TermDT] [datetime] NOT NULL , [HomePhoneNBR] [varchar] (15) NULL , [HomeADDR1] [varchar] (50) NOT NULL , [HomeADDR2] [varchar] (50) NOT NULL , [HomeCityNM] [varchar] (50) NOT NULL , [HomeStateCD] [char] (2) NOT NULL , [HomePostalCD] [varchar] (15) NOT NULL , [PersonnelLangCSV] [varchar] (500) NOT NULL , [PersonnelSlogan] [varchar] (500) NOT NULL , [BGColor] [varchar] (50) NOT NULL , [IsEAgent] [bit] NOT NULL , [IsArchAgent] [bit] NOT NULL , [IsOptOut] [bit] NOT NULL , [IsDispOnlyPrefFirstNM] [bit] NOT NULL , [IsHideMyListingLink] [bit] NOT NULL , [IsPreviewsSpecialist] [bit] NOT NULL , [AudioFileNM] [varchar] (100) NULL , [iProviderID] [int] NOT NULL , [DRENumber] [varchar] (10) NOT NULL , [AgentBrandedURL] [varchar] (255) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL , [IsDisplayAwards] [bit] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[PersonnelMLS] ( [PersonnelID] [int] NOT NULL , [SourceID] [int] NOT NULL , [AgentMlsNBR] [varchar] (20) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Office] ADD CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[OfficeProfile] ( [OfficeID] ) NOT FOR REPLICATION GO
alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile] GO
ALTER TABLE [dbo].[OfficeManagement] ADD CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY ( [JobTitleID] ) REFERENCES [dbo].[LookupJobTitle] ( [JobTitleID] ), CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[Office] ( [OfficeID] ) NOT FOR REPLICATION , CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY ( [PersonnelID] ) REFERENCES [dbo].[Personnel] ( [PersonnelID] ) ON DELETE CASCADE GO
alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office] GO
ALTER TABLE [dbo].[OfficeMls] ADD CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[Office] ( [OfficeID] ) NOT FOR REPLICATION GO
alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office] GO
ALTER TABLE [dbo].[PersonnelMLS] ADD CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY ( [PersonnelID] ) REFERENCES [dbo].[Personnel] ( [PersonnelID] ) NOT FOR REPLICATION GO
alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel] GO
Here's the query I'm having trouble with:
SELECT distinct Personnel.PersonnelID, Personnel.FirstNM, Personnel.LastNM, Office.OfficeNM, Office.OfficeID, OfficeMls.SourceID AS OfficeBoard, PersonnelMLS.SourceID AS AgentBoard FROM Personnel INNER JOIN OfficeManagement ON Personnel.PersonnelID = OfficeManagement.PersonnelID INNER JOIN Office ON OfficeManagement.OfficeID = Office.OfficeID INNER JOIN OfficeMls ON Office.OfficeID = OfficeMls.OfficeID INNER JOIN PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID where officemls.sourceid <> personnelmls.sourceid and office.officenm not like ('%admin%') group by PersonnelMLS.SourceID, Personnel.PersonnelID, Personnel.FirstNM, Personnel.LastNM, Office.OfficeNM, Office.OfficeID, OfficeMls.SourceID order by office.officenm
What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:
PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- 18205 Margaret Peggy Quattro Aventura North 650 906 908 18205 Margaret Peggy Quattro Aventura North 650 918 908 15503 Susan Jordan Blackburn Point 889 920 909 15503 Susan Jordan Blackburn Point 889 921 909 15503 Susan Jordan Blackburn Point 889 921 920 15279 Sandra Humphrey Boca Beach North 890 917 906 15279 Sandra Humphrey Boca Beach North 890 906 917 15279 Sandra Humphrey Boca Beaches 626 917 906 15279 Sandra Humphrey Boca Beaches 626 906 917 13532 Michael Demcho Boca Downtown 735 906 917 14133 Maria Ford Boca Downtown 735 906 917 19126 Michael Silverman Boca Glades Road 736 917 906 18920 Beth Schwartz Boca Glades Road 736 906 917
If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.
I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.
WITH cte_OrderProjectType AS ( select Orderid, min(TypeID) , min(CTType) , MIN(Area) from tableA A inner join tableB B ON A.PID = B.PID left join tableC C ON C.TypeID = B.TypeID LEFT JOIN tableD D ON D.AreaID = B.ID group by A.orderid )
This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..
Is there any way I can improve the query performance ?
Am I going about this the right way? I want to find pairs of entitiesin a table that have some relationship (such as a field being thesame), so Iselect t1.id, t2.id from sametable t1 join sametable t2 ont1.id<>t2.idwhere t1.fieldx=t2.fieldx ...The trouble is, this returns each pair twice, e.g.B CC BM NN MIs there a way to do this kind of thing and only get each pair once?Kerry
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
Just wanted to ask how to get all the duplicates records in a table. If I have say the following: col1 col2 col3 col4 col5 1 A1 ABC A21 AJ 1 A1 ABC A21 AJ 1 A2 ABC A21 AJ
The query should return the first 2 identical rows. I tried the following form but as you can see it has flaw that it gets the 3rd row as well simply because the outer select uses col1 as a condition which could belong to a "not completely" identical row. The inner select results in the distinct duplicate rows (2 in the table above, either of row 1 or 2 and row 3).
select * from table1 where col1 IN (select col1 from table1 group by col1, col2, col3, col4, col5 having count(*) > 1 )
my data is like so.IdDate Transaction5459/24/2003 3:01:08 PM13051:105469/24/2003 3:03:30 PM13051:105389/24/2003 2:53:31 PM13051:10025399/24/2003 2:54:57 PM13051:10021369/24/2003 10:08:45 AM13051:1011379/24/2003 10:08:47 AM13051:101I wanna run a query that gives meIdDate Transaction5459/24/2003 3:01:08 PM13051:105389/24/2003 2:53:31 PM13051:10021369/24/2003 10:08:45 AM13051:101The first record of the duplicates
I need to create my select statement to pull product details based on category, clientid (database runs two e-commerce sites).When i add the 'ClientOffers' table, it messes up the results. i get some duplicates.
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.
SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title HAVING COUNT(SubString(DiscNo, 0, 3)) > 1
SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist ORDER BY Artist, Title, DiscNo
Drop TABLE #TempArtistTitle GO
See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.
SC8151-10 - Garth Brooks - Friends In Low Places SC8125-04 - Garth Brooks - Friends In Low Places LG5003-07 - Garth Brooks - Friends In Low Places
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns select @rowId = min(RowId), @MaxRowId = max(RowId) from #tblFieldNames
-- Loop through fields and build Sql string while @RowId <= @MaxRowId BEGIN SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
If I run the following select statment against the appropriate table it returns the duplilcate records in the result set. However, from this list I want to add an additional select statement embedded into the query that will actually return only those records with the most current syscreated date.
Example of script I'm using---
select cmp_fadd1, syscreated, cmp_name, cmp_code from cicmpy where cmp_fadd1 in (select cmp_fadd1 from cicmpy group by cmp_fadd1 having count(1) = 2) order by cmp_fadd1,syscreated desc
The results is:
Address Syscreated date Customer 1622 ONTARIO AVENUE 2005-06-15 22:19:45.000 RELIABLE PARTSLTD 1622 ONTARIO AVENUE 2004-01-22 18:10:05.000 RELIABLE PARTS LTD PEI CENTER 2006-01-05 22:03:50.000 P.G. ENERGY PEI CENTER 2004-01-22 17:57:56.000 P.G. ENERGY
From this I want to be able to select ONLY those records with the most current syscreated date or those records with 2005-06-15 and 2006-01-05
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,
How do I eliminate others from viewing one of the 2 databases on our production server???Is there any security not to allow all users to including sa and developers not to access one of the 2 databases on our server.. The other of the 2 databases can be accessed.... Please advise
I'm in the process of doing a major data clean up and I'm just wondering how I would go about eliminating some redundant data.
The Table Layout
Contracts
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM ======================================================= 0 1234567 091885 A 1 1234567 091885 A 2 1111111 111111 B 3 1234567 081205 A
Equipment
EQUIPID DEVICENAME CNTRID CUSTOMNUM ======================================================= 0 DEVICE1 0 A 1 DEVICE2 2 B 2 DEVICE3 1 A 3 DEVICE4 3 A
You will notice that each customer may have multiple devices. Each device may be tied to a contract, and each contract may have one or more devices tied to it.
In the example above, you will notice in the contracts table the contracts with the IDs 0 and 1.
Fig 1.
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM ======================================================= 0 1234567 091885 A 1 1234567 091885 A
These contracts have the exact same information.
Furthermore, if you look down the table you will notice the contract with the ID 3.
Fig 2.
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM ======================================================= 3 1234567 081205 A
This contract shares the same contract and customer number, but has a different start date.
Now lets take a look devices in the equipment table that refer to these records.
EQUIPID DEVICENAME CNTRID CUSTOMNUM ======================================================= 0 DEVICE1 0 A 2 DEVICE3 1 A 3 DEVICE4 3 A
You will notice that DEVICE1 and DEVICE 3 refer to the contract records that contain identical data. (As shown in 'Fig 1')
My question is as follows:
How do I eliminate the any duplicate records from the contracts table, and update the records in the equipment table with id of the left over contract.
Results Should be as follows:
Contracts
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM ======================================================= 0 1234567 091885 A 2 1111111 111111 B 3 1234567 081205 A
Equipment
EQUIPID DEVICENAME CNTRID CUSTOMNUM ======================================================= 0 DEVICE1 0 A 1 DEVICE2 2 B 2 DEVICE3 0 A 3 DEVICE4 3 A
Any help you may provide would be greatly appreciated!
I have a SQL statement with two left outer joins which connects 3 tables. Vendors, Tracking & Activity. For whatever reason, even though each is a one-to-many relationship, I am able to join 2 tables (from Vendors to Tracking) without an issue. when I then join Activity, I get a Cartesian product.I suspected that 'DISTINCT'.
SELECT DISTINCT CASE WHEN `vendor`.`companyname` IS NULL then 'No Company Assigned' ELSE `vendor`.`companyname` END AS companyNameSQL, `tracking`.`pkgTracking`, CASE
This above query returns all requests that meets atleast one criteria. How do i edit my query such that i get requests that meet both criteria and the result set looks like below
edit: this came out longer than I thought, any comments about anythinghere is greatly appreciated. thank you for readingMy system stores millions of records, each with fields like firstname,lastname, email address, city, state, zip, along with any number of userdefined fields. The application allows users to define message templateswith variables. They can then select a template, and for each variablein the template, type in a value or select a field.The system allows you to query for messages you've sent by specifyingcriteria for the variables (not the fields).This requirement has made it difficult to normalize my datamodel at allfor speed. What I have is this:[fieldindex]id int PKname nvarchartype datatype[recordindex]id int PK....[recordvalues]recordid int PKfieldid int PKvalue nvarcharwhenever messages are sent, I store which fields were mapped to whatvariables for that deployment. So the query with a variable criterialooks like this:select coalesce(vm.value, rv.value)from sentmessages sminner join variablemapping vm on vm.deploymentid=sm.deploymentidleft outer join recordvalues rv onrv.recordid=sm.recordid and rv.fieldid=vm.fieldidwhere coalesce(vm.value, rv.value) ....this model works pretty well for searching messages with variablecriteria and looking up variable values for a particular message. thebig problem I have is that the recordvalues table is HUGE, 1 millionrecords with 50 fields each = 50 million recordvalues rows. The value,two int columns plus the two indexes I have on the table make it into abeast. Importing data takes forever. Querying the records (with a fieldcriteria) also takes longer than it should.makes sense, the performance was largely IO bound.I decided to try and cut into that IO. looking at a recordvalues tablewith over 100 million rows in it, there were only about 3 million uniquevalues. so I split the recordvalues table into two tables:[recordvalues]recordid int PKfieldid int PKvalueid int[valueindex]id int PKvalue nvarchar (unique)now, valueindex holds 3 million unique values and recordvaluesreferences them by id. to my suprise this shaved only 500mb off a 4gbdatabase!importing didn't get any faster either, although it's no longer IO boundit appears the cpu as the new bottleneck outweighed the IO bottleneck.this is probably because I haven't optimized the queries for the newtables (was hoping it wouldn't be so hard w/o the IO problem).is there a better way to accomplish what I'm trying to do? (eliminatethe redundant data).. does SQL have built-in constructs to do stuff likethis? It seems like maybe I'm trying to duplicate functionality at ahigh level that may already exist at a lower level.IO is becoming a serious bottleneck.the million record 50 field csv file is only 500mb. I would've thoughtthat after eliminating all the redundant first name, city, last name,etc it would be less data and not 8x more!-GordonPosted Via Usenet.com Premium Usenet Newsgroup Services----------------------------------------------------------** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **----------------------------------------------------------http://www.usenet.com
Being one step removed from innumerate, I was wondering whether there was a more elegant way to avoid divide by zero error instead of trudging through a bunch of isnulls.
My intuition tells me that since multiplication looks like repeated addition, that maybe division is repeated subtraction? If that's true is there a way to finesse divide by zero errors by somehow reframing the statement as multiplication instead of division?
The sql statement that is eating my kishkas is
cast(1.0*( (ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/ ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)) as decimal(10,4)) TotalLossRatio
Is there a way to nucleate the error by restating the division? My assertion underlying this statement is that the a alias represents a premium paid, so between medical, pharmacy and dental, there MUST BE at least one premium paid, otherwise you wouldn't be here. the b alias is losses, so likewise, between medical, pharmacy and dental, there MUST BE at least one loss (actually, it just occurred to me that maybe there are no losses, but that would be inconceivable, but ill check again)) so that's when it struck me that maybe there's a different way to ask the question that obviates the need to do it by division.
I am querying several tables and piping the output to an Excel spreadsheet. Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly): OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002
I only want the output to be: OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393
I am new to sql server and I am having deficulties writing sql script to perform the following: 1) Merging data from two tables A and B 2) Eliminate duplicate present in table B (Conditions to satisfy for dublicate:If similar address is found in both tables AND class type in Table A =1 3) merge data related to dup(eliminated records) to new table. Not sure if we can eliminate records first before merging two tables. Tables are as follow:
Table A Fields: ID, NAME, Address, city, zip, Class type Value:123, John, 123 Main, NY, 71690,1 Value:124, Tom, 100 State, LA, 91070,0
Table B Field: ID, NAME, Address, city, zip, Class Type Value:200, Tim, 123 Main, NY, 71690,0 (duplicate; satisfied both conditions and left out in final table) Value:124, Jack, 100 State, LA, 91070,0 (same condition but second condition is not met) Value:320,Bob, 344 coast hwy, slc, 807760,0
Final Table: Field: ID, NAME, Address, city, zip, Class Type Value:123, John, 123 Main, NY, 71690,1 (should also show t Value:124, Tom, 100 State, LA, 91070,0 Value:124, Jack, 100 State, LA, 91070,0 Value:320,Bob, 344 coast hwy, slc, 807760,0
Table d:(relate to table A:showing all products that are related to table A) table_A.ID, Products 123, Paper 1 123, paper 2
Table e:(relate to table B: showing all products that are related to table B) table_B.ID, Products 200, Paper 3
Final Table: ID, Product 123, Paper 1 123, Paper 2 123, Paper 3 (changing table b id to table a)
Would appreciate any help writing script to perform such transformation. Thanks
I'm trying to eliminate the duplicate 'URL' rows in the query:
SELECT ni.[Id], ni.[Abstract], ni.[MostPopular], ni.[URL] FROM dbo.[NewsCategory] nc WITH (READUNCOMMITTED) INNER JOIN dbo.[NewsItem] ni WITH (READUNCOMMITTED) ON nc.[Id] = ni.NewsCategoryId WHERE --nc.[ProviderId] = @ProviderId --AND ni.[URL] in ( select DISTINCT URL from dbo.NewsItem where mostpopular = 1 -- OR mostemailed = 1 ) ORDER BY ni.[DateStamp] DESC
If you look at this line in the query :
select DISTINCT URL from dbo.NewsItem where mostpopular = 1
IF i run this query alone it will return 8 unique rows. I expect that the SELECT IN statemnet would help return a distinct set but it doesn't. This entire query returns like 20 rows with duplicate rows.
The reason why I can't do a distinct in the first set of columns is because the column ni.[Abstract] is TEXT and it says that data type is NOT COMPARABLE.
Hi i have a table value which contains value ----- a a a b b b c c c
Now i need to have the results as
a 1
b 1
c 1
I tried using distinct.But OLEDB returns error that invalid syntax.It doesn't support distinct keyword.Actually i read these table from a file thru OLEDB.Not from a database.Any idea ? Thanks in Advance
Suppose I have users that can belong to organizations. Organizationsare arranged in a tree. Each organization has only one parentorganization but a user maybe a member of multiple organizations.The problem that I'm facing that both organizations and individualusers may have relationships with other entities which aresemantically the same. For instance, an individual user can purchasethings and so can an organization. An individual user can havebusiness partners and so can an organization. So it seems that I wouldneed to have a duplicate set of link tables that link a user to apurchase and then a parallel link table linking an organization to apurchase. If I have N entities with which both users and organizationsmay have relationships then I need 2*N link tables. There is nothingwrong with that per se but just not elegant to have two differenttables for a relationship which is the same in nature, e.g.purchaser->purchaseditem.One other approach I was thinking of is to create an intermediateentity (say it's called "holder") that will be used to hold referencesto all the relationships that both an organization and an individualmay have. There will be 2 link tables linking organizations to"holder" and users to "holder". Holder will in turn reference thepurchases, partners and so on. In this case the number of link tableswill be N+2 as opposed to 2*N but it will have a performance cost ofan extra join.Is there a better way of modelling this notion of 2 different entitiesthat can possess similar relationships with N other entities?
I have an UPDATE statement that joins two table by SendId. One table, I'll call it T1, has a clustered index on SendId ASC. The other table I will call T2 also has a clustered index on SendID ASC. All the columns from T2 are used to update T1. The execution plan shows a Clustered index scan on T2 and a Clustered Index Seek on T1 going into a Nested Loops inner join. Immediately following is a Distinct Sort that is done on SendId ASC. Why the Distinct SORT if the tables are already ordered by SendID?
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float)) FROM [dbo].[LaborCosts] WHERE ppty = 'ga' AND PL = 'allctd ktchn expns' AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
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?
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
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 ?
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
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
