Hi everyone.
How can I get the unique row from a table which contains multiple rows
that have exactly the same values.
example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )
insert into test values (1,2,3)
insert into test values (1,2,3)
i want to remove whichever of the rows but I want to retain a single
row.
TIA
Diego
I've got the following table data:116525.99116520.14129965.03129960.12129967.00And I need to write a query to return only rows 2 and 4, since theremaining rows have duplicate IDs. I've tried the Group By, but amhaving no luck.Thanks!
View 5 Replies View RelatedI have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove...
my tables is like that
Names
------------
Rakesh
Rakesh
Rakesh Kumar Sharma
Rakesh Kumar Sharma
Baburaj
Raghu
Raghu
and Output of query should be like that
Names
-----------
Rakesh
Rakesh Kumar Sharma
Baburaj
Raghu
Thanks in advance
Hi guys
I have been using SQL server 2005. I have got a huge table with about 1 million rows.
Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that??
Is there a query in SQL to remove duplicate rows???
thanks
Mita
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:-
AID BID BNAME DDATE DAUTHOR
1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com
1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com
1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com
1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com
1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com
1 2 xyzabc NULL NULL
2 3 pqrlmn NULL NULL
2 4 cdefgh NULL NULL
Now, I want unique rows from the above result set like :-
AID BID BNAME DDATE DAUTHOR
1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com
1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com
2 3 pqrlmn NULL NULL
2 4 cdefgh NULL NULL
I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
I have a dataset in my report that pulls 10 fields. One of the tablixes on report should display only 8 of the 10 fields and this is causing the duplicate records to show up on the tablix.I tried hide duplicates option for the entire details row and have set the scope to "Details". It did not work. I am getting the data from a stored procedure and cannot do much on that.
View 7 Replies View Related
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
HI All,
I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..
Thnx
;)
Shani
I am a newb at ms sql and was hoping someone could help me
eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because
the duplicates are from the PRODUCT.PRODUCT that have more than 1 image.
If anyone could rewrite this statement so I can learn from this, it would
be most appreciated!
Thank you for your time
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>
i'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks
I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with
ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London
The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?
Cheers
Hi
I have a data in one table like below.
EDITION PRODUCT INSERTDATE
---------- ------------ ----------------------
CNE TN-Town News 12/19/2007 12:00:00 AM
TN TN-Town News 12/19/2007 12:00:00 AM
What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.
How to do that?. Can anybody help me?
Thanks
Venkat
Below is a raw data with SQL script to create the temp table. This table is a scale down version of what I am working with. I am taking the addresses from several different sources and putting them in a temp table. Now, I need to remove the duplicate addresses for each ID. Duplicate addresses are okay for different IDs.
Raw Data
IDAddr1 CityStateZip
26205 N Main STChicagoIL52147
26205 N Main STChicagoIL52147
2685 Park AveChicagoIL52147
3535 Main StAustinTX78715
35976 Ponco StDallasTX79757
359587 MopacAustinTX78715
4558741 Len LnDaytonFL74717
455518 Spring DrDaytonFL74717
45585 Park AveChicagoIL52147
Desired Result
IDAddr1 CityStateZip
26205 N Main STChicagoIL52147
2685 Park AveChicagoIL52147
3535 Main StAustinTX78715
35976 Ponco StDallasTX79757
359587 MopacAustinTX78715
4558741 Len LnDaytonFL74717
455518 Spring DrDaytonFL74717
45585 Park AveChicagoIL52147
CREATE TABLE #tmpTable(
[ID] Int,
[Addr1] [varchar](15) NULL,
[City] [varchar](9) NULL,
[State] [varchar](2) NULL,
[Code] .....
I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates:
Code Snippet
DELETE FROM _email WHERE _email.eid IN
(
SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
)
AND _email.eid NOT IN
(
SELECT Min(eid) FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
GROUP BY emailaddress
);
This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster?
I am running SQL Server 2000
Thanks in advance
Hi All ,
I have a CSV file which contains some duplicate record and i have to load this file in SQL server database using SSIS package .
What i have to do is read the file and if the same record entry is occur more than 10 times for a particular unique combination ( like ID , Date , Time ) then i need to take only one record for that occurance.
Plesae suggest , Help ,
Regards,
Ashish
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
I am working SQL Server 2005 and One Table Which contain only one column without primary keyNow I want to remove all duplicate value from that table with only single query
View 2 Replies View RelatedI found some duplicate data as I was going thru the logic of a data pump. The entire row is not duplicated however.I would like to delete only the one row.
This is a sample of the data:
DECLARE @SomeData TABLE
(
FirstName varchar(25)
, MiddleName varchar(25)
, LastName varchar(25)
, StreetAddress varchar(25)
, Suite varchar(25)
, City varchar(25)
, [State] varchar(25)
, PostalCode varchar(10)
[code]...
As you can see, Joe Smith has two rows, but only one of the rows is complete. I would like to delete only the row that has a NULL value in the phone and area code for Joe Smith. There are a few thousand rows that are like this. They have duplicates all but the area code and phone number.I am used to using a CTE to remove duplicates, but I am a little lost on this one. The things that I have tried, have not worked exactly as I planned.
Whenever I try to join multiple Tables it will appear in duplicate.
Example:
ID SURNAME NAME SEX DOB
1YAHAYISAMALE05.02.19911NIGERIAKADUNA12 RIMI ROAD
1YAHAYISAMALE05.02.19912USALONDON12 NEW JESSY
1YAHAYISAMALE05.02.19913BENINTOGO12 KIMOI ROAD
1YAHAYISAMALE05.02.19914KENYAYANE8 MUTU AVENUE
1YAHAYISAMALE05.02.19915BRAZILLAZZIO4 ZINO AVENUE
2IBRAHIMJAMILAFEMALE04.05.20011NIGERIAKADUNA12 RIMI ROAD
[Code] ....
I have a database being populated by hits to a program on a server.The problem is each client connection may require a few hits in a 1-2second time frame. This is resulting in multiple database entries -all exactly the same, except the event_id field, which isauto-numbered.I need a way to query the record w/out duplicates. That is, anyrecords exactly the same except event_id should only return one record.Is this possible??Thank you,Barry
View 14 Replies View RelatedThere's some SQL below (T-SQL) & I'm wanting to have this result set
grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate
values. I only want to get rows for one instance of the Venue_ID (per
BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks,
Darren
darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID,
Booking_Header.Booking_Header_Description,
Booking_Header.BStat_ID, Booking_Header.BT_ID,
Booking_Header.Tagged, Booking_Header.Status_Timestamp,
Booking_Header.Start_Date, Booking_Header.Days_Qty,
Proposal.PPL_ID, Proposal.PPL_Status,
Booking_Component.BCOM_ID,
Booking_Component.Component_Description,
Booking_Component.Venue_ID, Venue.Venue_Code,
Venue.Description, Address.Address_ID, Address.Town,
Booking_Status.BStat_Description,
Booking_Type.Type_Description
FROM dbo.Booking_Header INNER JOIN
dbo.Proposal ON
dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN
dbo.Booking_Component ON
dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER
JOIN
dbo.Venue ON
dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN
dbo.Address ON
dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN
dbo.Booking_Status ON
dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER
JOIN
dbo.Booking_Type ON
dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID
WHERE (dbo.Proposal.PPL_Status = 1) AND
(dbo.Booking_Header.BH_ID = 10)
Thanks,
Darren
Is there a way to check if duplicates exists in the incoming textfiles????
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
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!
View 4 Replies View RelatedI would like to know how to produce the following in a query.
Parent Child
---------------
Dave Sarah
.......... Brad
.......... Alice
.......... Hanna
Rather than:
Parent Child
---------------
Dave Sarah
Dave Brad
Dave Alice
Dave Hanna
Boss,
Forget abt all.
My input table consist of ONLY TWO columns. And this is my table
(INPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:45 SELL
17:46 SELL
17:47 BUY
17:48 BUY
17:49 SELL
17:50 SELL
When ever ACTION columns data is repeating remove that repetitive data rows.
(i mean to say Bold characters rows want to remove)
And finally my output should like this
(OUTPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:47 BUY
17:49 SELL
Ok, so I've been racking my brains on this one for a while now and figured it was time to ask for some help...
I have a table named tblAppInfo that looks something like this:
Code Block
user_name app_name app_ver
User1 MS Word 2000
User2 MS Word 2000
User3 MS Excel 2000
User4 MS Excel 2000
User5 MS Excel 2000
User6 MS Excel 2000
User7 MS Outlook 2000
User8 MS PowerPoint 2000
User9 Adobe Acrobat 5.0
User10 Adobe Acrobat 6.0
User11 Adobe Acrobat 7.0
What I want to do is remove any rows that repeat the app_name and app_ver more than twice. Basically in the example above, it would remove the lines that have MS Excel 2000 (as it's listed 4 times). MS Word 2000 would stay as it's only listed twice. As would Outlook and PowerPoint. All 3 Adobe's would also stay as their versions are different.
I've tried the following but it still repeats applications that occur more than twice:
Code BlockSELECT user_name, app_name, app_ver
FROM tblAppInfo
GROUP BY app_name, app_ver, user_name
having count(distinct app_name + app_ver + tblAppInfo.host_name) < 3
ORDER BY app_name, app_ver
I've also tried using nested select statements and everything else in my arsenal to no avail. I hope someone here will be able to help me out as I'm afraid I'll go bald if I try anymore!
Thanks in advance!
Tom
Hi,
I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.
E.g. If I have the following data:
Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald
Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...
Then I would like the following output:
Main Output
Key Name
--- ----
2 Jamie
Second Output
Key Name
--- ----
1 Steve
3 Donald
Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated!
Thanks in advance,
Lawrie
If we want to remove the duplicate row and leave only one row instead of 2 or 3 rows for example with the same column values.
2/ The same question but when all the columns of the row are duplicate except the id field.
Thanks a lot.
Hi,I've got a db table containing 5 columns(excluding id) consisting of1.) First Half of a UK postcode2.) Town name to which postcode belongs3.) Latitude of Postcode4.) Longitude of Postcode5.) Second Part of the PostcodeI want to select columns 1,2,3 and 4, but once only. There are oftenseveral entries where 1 and 2 are the same but 3 and 4 are differenti.e.WA1Bewsey and Whitecross53.386492-2.596847WA1Bewsey and Whitecross53.388203-2.590961WA1Bewsey and Whitecross53.388875-2.598504WA1Fairfield and Howley53.388455-2.581701WA1Fairfield and Howley53.396117-2.571789My current query isSELECT DISTINCT Postcode, Town, latitude, longitudeFROM PostcodeWHERE Postcode.Postcode = 'wa1'ORDER BY Postcode, TownHowever as latitude and longitude differ on each line DISTINCT doesnot do what I'm looking for.Can anybody suggest a way changing the query to just give the firstinstance of each Postcode/Town combo?I.E.WA1Bewsey and Whitecross53.386492-2.596847WA1Fairfield and Howley53.388455-2.581701Many thanks!Drew
View 3 Replies View RelatedHi, i am very struggle to find out.
My input table consist of TWO columns. And this is my table
(INPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:45 SELL
17:46 SELL
17:47 BUY
17:48 BUY
17:49 SELL
17:50 SELL
When ever ACTION columns data are repeating remove that repetitive data rows.
(i mean to say Bold characters rows want to remove)
And finally my output should like this
(OUTPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:47 BUY
17:49 SELL