DISTINCT Not Working To Eliminate Duplicate Column Names
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
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
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:
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.
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.
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?
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 .
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..
I have a lot of different data flows that need "Derived Column". There are maybe only 5 different such "Derived Column" but they appear many times. Is there a way to eliminate all that double work? It should be something that does not take me more time to do than just duplicating all the "Derived Columns".
I built a package to transform data from flatfile into temp table. Then execute a stored procedure to tranform from the temp table into the real table. Since the real table have primary keys so it goes failed when there're duplicate rows from temp table. I let the temp table has no primary key. If I had to check the temp table for duplicate rows it would be using cursor through the data and the package will get slow. Is there task in SSIS to identify the duplicate data and eliminate it without using cursor ? Thanks in advance,
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.
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
I have a client table containing Client ID, DOB First Name and Last Name Etc. I would like to run a query to identify rows where the Last name is similar and the DOB is a match but to also include the client ID and name in the row. I have used the below query with the soundex function, but I would like to alter it to show the clientID and name.
Any help would be really appreciated.
SELECT DOB, soundex(Lastname) AS NameSoundex, count(*) as Duplicates
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
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.
WHen researching an index name problem, I found duplicate index names in sysindexes, referencing the same table. I notice that one of the indexes has a status = 2097154 and a indid of 2 while the other has a status of 0 and a indid of 0. I believe these are duplicates. Anybody have an idea if these really are and what the status and indid fields mean?
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
Hi all, I am using SQLEXPRESS 2005 and can't figure out why my SQL query is not working.Select DISTINCT ads.clientid, ads.ad, ads.adid, ads.cost, ads.description, ads.bold, ads.info, ads.contact, ads.coupon, ads.logo, ads.map, ads.webpage, ads.weburl, tclients.clientname, tclients.clientphone, tclients.addNumber, tclients.addStreet, tclients.addsuite, tclients.addcity, tclients.addstate, tclients.addzip from tclients, ads where tclients.clientid=ads.clientid AND AdActive=1 ORDER BY COST DESC I expect only one row to be returned for each ads.clientid. However, every single record that matches the query is being returned, which results in multiple records with the same clientid.This happens if it is ads.clientid or tclients.clientid. Martin
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.
I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.
I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.
I am trying to run a select query and getting the following error: Server: Msg 1013, Level 15, State 1, Line 12 Tables or functions 'PatientVisit' and 'PatientVisit' have the same exposed names. Use correlation names to distinguish them.
Not sure what I am doing wrong! Here is the query. Can anyone point me int the right direction?
SELECT Batch.Entry AS [Date Of Entry], PatientProfile.[Last] + ', ' + PatientProfile.[First] AS Name, MedLists.Description AS [Adjustment Type], PatientVisit.TicketNumber, PatientVisitProcs.Code AS [Procedure Code], TransactionDistributions.Amount AS [Adjustment Amount], PatientVisitProcs.DateOfServiceFrom, ISNULL(CONVERT(varchar(255), Transactions.Note), ' ') AS Notes, DoctorFacility.DotID, DoctorFacility.ListName as DrName, PatientVisit.DoctorID as DrID FROM PaymentMethod INNER JOIN VisitTransactions ON PaymentMethod.PaymentMethodId = VisitTransactions.PaymentMethodId INNER JOIN Transactions ON VisitTransactions.VisitTransactionsId = Transactions.VisitTransactionsId INNER JOIN TransactionDistributions ON Transactions.TransactionsId = TransactionDistributions.TransactionsId INNER JOIN Batch ON PaymentMethod.BatchId = Batch.BatchId INNER JOIN PatientVisit ON VisitTransactions.PatientVisitid = PatientVisit.PatientVisitId Inner Join PatientVisit ON DoctorFacility.DoctorFacilityID = PatientVisit.DoctorID INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId INNER JOIN MedLists ON Transactions.ActionTypeMId = MedLists.MedListsId INNER JOIN PatientVisitProcs ON TransactionDistributions.PatientVisitProcsId = PatientVisitProcs.PatientVisitProcsId
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert Insert into Employee values('aa', 'T', 'qqq') Insert into Employee values('aa' , 'F' , 'qqq') Insert into Employee values('bb', 'F' , 'eee') Insert into Employee values('cc' , 'T' , 'rrr') Insert into Employee values('cc' , 'pp' , 'aaa') Insert into Employee values('cc' , 'Zz' , 'bab') Insert into Employee values('cc' , 'ZZ' , 'bac') For select select col1,MAX(col2) as Col2,Max(Col3) as Col3 from Employee group by Col1
I have a table in which a non-primary key column has a unique index on it. If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing
dbo_dbWafer_Slicing
WaferID SawDate SawRunNumber Pass
03-157.05 1/8/2008 9:54:00 AM 03-157 0
03-157.03 1/8/2008 9:53:00 AM 03-157 -1
03-157.04 1/8/2008 9:53:00 AM 03-157 0
03-157.02 1/8/2008 9:52:00 AM 03-157 -1
03-157.01 1/8/2008 9:50:00 AM 03-157 -1
03-165.06 1/4/2008 10:46:00 AM 03-165 0
03-165.07 1/4/2008 10:46:00 AM 03-165 0
03-165.04 1/4/2008 10:45:00 AM 03-165 0
03-165.05 1/4/2008 10:45:00 AM 03-165 0
03-165.02 1/4/2008 10:44:00 AM 03-165 0
03-165.03 1/4/2008 10:44:00 AM 03-165 0
03-165.01 1/4/2008 10:43:00 AM 03-165 0
So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help.
I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
