Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

remove duplicates IF matches Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 9:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
So I have the following statement:


SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID
FROM HMD hm
join Correspondences_All ca on hm.[caseid]=ca.caseid
where ca.supervisor ='Iamsomeone' and [comments] not in ('null','')

It returns a bunch of duplicates in the comments, this is expected, but what I want to filter out is just this I need the query to return only one unique set of the comments UNLESS the userlogin is different. Here is a small subset of the select to give you an idea of what I mean.

CaseID: Comments: Userlogin
1243546457 Great help UserA
3123234353 AWESOME UserB
2131314242 Support was terrible UserC
2131314242 Support was terrible UserC
2131314242 Support was terrible UserC
2131314242 Support was terrible UserA
3453453453 Could have been better UserB

note the item in bold, this is the variable I need to consider.

Anyone able to help me with this one?
Post #1427753
Posted Wednesday, March 6, 2013 10:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
Something like this:


--CaseID: Comments: Userlogin
--1243546457 Great help UserA
--3123234353 AWESOME UserB
--2131314242 Support was terrible UserC
--2131314242 Support was terrible UserC
--2131314242 Support was terrible UserC
--2131314242 Support was terrible UserA
--3453453453 Could have been better UserB

declare @TestData table (
CaseID bigint,
Comments varchar(32),
UserLogin varchar(16)
);

insert into @TestData
values (1243546457, 'Great help', 'UserA'),
(3123234353, 'AWESOME', 'UserB'),
(2131314242, 'Support was terrible', 'UserC'),
(2131314242, 'Support was terrible', 'UserC'),
(2131314242, 'Support was terrible', 'UserC'),
(2131314242, 'Support was terrible', 'UserA'),
(3453453453, 'Could have been better', 'UserB');

with BaseData as (
select
CaseID,
Comments,
UserLogin,
rn = row_number() over (partition by CaseID, Comments, UserLogin order by UserLogin)
from
@TestData
)
select
CaseID,
Comments,
UserLogin
from
BaseData
where
rn = 1;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427758
Posted Wednesday, March 6, 2013 10:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 1,126, Visits: 1,590
Hello,

Since you have 20 points, I assume that you are not new here have a relative idea about posting data on forums. If you don't then there is a detailed article about it in my signature. If you still don't get it then this is how its done :

Create table Ex 
(
CaseID Varchar(20),
Comments Varchar(100),
Userlogin Varchar(10)
)

Insert Into Ex
Select '1243546457', 'Great help', 'UserA'
Union ALL
Select '3123234353', 'AWESOME', 'UserB'
Union ALL
Select '2131314242', 'Support was terrible', 'UserC'
Union ALL
Select '2131314242', 'Support was terrible', 'UserC'
Union ALL
Select '2131314242', 'Support was terrible', 'UserC'
Union ALL
Select '2131314242', 'Support was terrible', 'UserA'
Union ALL
Select '3453453453', 'Could have been better', 'UserB'

Select CaseId, Comments, UserLogin
From
(
Select *, ROW_NUMBER() Over(Partition By CaseId, Comments, UserLogin Order By CaseId) As rn From Ex
) As a
Where rn = 1



The above is the code which includes the table script, sample data and the query for your requirement.
Hope this is what you are looking for.
From next time please help us by posting table scripts and sample data as a string of insert statements so that people don't have to type it themselves and can rather use the script provided by you.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1427759
Posted Wednesday, March 6, 2013 10:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 1,126, Visits: 1,590
Oh shoot!!!...so much for typing so much
Lynn posted while I was typing....


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1427760
Posted Wednesday, March 6, 2013 11:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Thanks all, I really should have been clearer. The data is very much dynamic I can't predict what to expect from the comments. I was giving you a sample of how the data might display.
Post #1427776
Posted Wednesday, March 6, 2013 11:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 1,126, Visits: 1,590
Brad Marsh (3/6/2013)
Thanks all, I really should have been clearer. The data is very much dynamic I can't predict what to expect from the comments. I was giving you a sample of how the data might display.


So...don't the above suggestions get you the result that you are expecting from your actual data?.....


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1427788
Posted Thursday, March 7, 2013 12:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
No So what I mean by comments is the comments field in the SQL.

So all of this data I have is created by customers, and because of that there is no static or same comment ever made in the comments, they are rather unique for that reason I can't add it as predefined data as suggested in the examples.

This all needs to be done dynamically, no to mentions there is well over 3K rows and will be ever growing.

Does this make any sense?
Post #1427802
Posted Thursday, March 7, 2013 1:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 1,126, Visits: 1,590
Let me get this right....from what i understood....a user may have umpteen no. of comments and the comments could all b different so you cant group using the comments column.

So, what you want is a result set that displays only one comment per user id.....Is that correct??


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1427827
Posted Thursday, March 7, 2013 5:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
Brad Marsh (3/7/2013)
No So what I mean by comments is the comments field in the SQL.

So all of this data I have is created by customers, and because of that there is no static or same comment ever made in the comments, they are rather unique for that reason I can't add it as predefined data as suggested in the examples.

This all needs to be done dynamically, no to mentions there is well over 3K rows and will be ever growing.

Does this make any sense?


Nope. Why did you provide this as a sample if it was inaccurate? The code provided was based on your sample.



Here is a small subset of the select to give you an idea of what I mean.

CaseID: Comments: Userlogin
1243546457 Great help UserA
3123234353 AWESOME UserB
2131314242 Support was terrible UserC
2131314242 Support was terrible UserC
2131314242 Support was terrible UserC
2131314242 Support was terrible UserA
3453453453 Could have been better UserB

note the item in bold, this is the variable I need to consider.



Of course, your sample also has fewer columns of data than your query. Take what was provided and adjust it to suit your needs. If you have problems, post back and be sure to provide better sample data. Also, look at how the code provided was posted. It included the ddl (in this case I used a table variable, you could always use a temporary table or permanent table instead), sample data as insert statements. You can easily cut./paste/run the code to see how it works.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427910
Posted Thursday, March 7, 2013 5:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Thanks Lynn I can appreciate what your saying but the reason I gave you that dummy data is because the data is confidential, I can not simply throw it up.

I also appreciate the fact that you say simply reuse the query provided, but I just don't see how that is possible to scale with. As the data is dynamic it is driven from a form that is completed and then submitted into the database. there could be hundreds of additions to this per day, so I am not sure that this can scale moving forward.

Any ideas on how I can achieve the outcome, with something that can better scale for these requirements? If you need read data, I can give it to you, but I am afraid i will have needed to blank some items out (which may make the data rather useless).

Cheers,
Brad
Post #1428330
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse