Removing Duplicate Rows In Grouping

Feb 14, 2012

i've to generate a notepad using this query in vb.net :

strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _

[Code]....

In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.

Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.

how to group the "d.amount" without having repetition in the "d.shareholderid" ??

View 5 Replies


ADVERTISEMENT

Removing Duplicate Rows

Dec 3, 2005

Hi,

Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.

Table :

create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go

Please advise,

Thanks,
MiraJ

View 7 Replies View Related

SQL: Removing Partial Duplicate Rows

Dec 24, 2007

Hi,
 I have results from a survey in a table, every entry is assigned a unique ID. I want to remove duplicate entries based on the survey data but not on the unique ID (obviously).
So far I have...SELECT DISTINCT RespondantID, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13
FROM Results
But that gives...




1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1

2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2

3 - Martin
2
2
2
2
2
2
2
2
2
2
2
2
2
I.e. in the above example, it would seem that 'Martin' submitted his data once and then submitted it again with his name.
 How can I remove the '2 - Anonymous' frrom the data set?
 Thanks!

View 8 Replies View Related

Removing Partially Duplicate Rows For Resultset ? Help Needed.

Feb 2, 2008

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. Please provide separate solutions for SqlServer2000/2005. 
I have four tables namely – Forums,Topics,Threads and Messages  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 Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
                 SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
             INNER JOIN Threads ON Topics.topicid = Threads.topicid
                 INNER JOIN Messages ON Threads.threadid=Messages.threadid
             WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid 
Whose result set is as below:- 




forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
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

I want the result set as follows:- 




forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
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 message) from the Messages table as shown above. 
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:- 
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
             SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
             INNER JOIN Topics ON Topics.topicid = Threads.topicid
             WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
             ) AS LastPosts
             ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid 
Whose result set is as:- 




ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads

1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
Developers
2
JSP
NULL
NULL
0

1
Developers
3
EJB
NULL
NULL
0

1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
Developers
5
AWT
NULL
NULL
0

1
Developers
6
Web Services
NULL
NULL
0

1
Developers
7
JMS
NULL
NULL
0

1
Developers
8
XML,HTML
NULL
NULL
0

1
Developers
9
Javascript
NULL
NULL
0

2
Database
10
Oracle
NULL
NULL
0

2
Database
11
Sql Server
NULL
NULL
0

2
Database
12
MySQL
NULL
NULL
0

3
Desginers
13
CSS
NULL
NULL
0

3
Desginers
14
FLASH/DHTLML
NULL
NULL
0

4
Architects
15
Best Practices
NULL
NULL
0

4
Architects
16
Longue
NULL
NULL
0

5
General
17
General
NULL
NULL
0  
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__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
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].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
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].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
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].[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 
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].[Messages] (
            [msgid] [int] IDENTITY (1, 1) NOT NULL ,
            [threadid] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [date] [datetime] NULL ,
            [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Messages] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [msgid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO 
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Messages] ADD
             FOREIGN KEY
            (
                        [threadid]
            ) REFERENCES [dbo].[Threads] (
                        [threadid]
            )
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'); 
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
 

View 5 Replies View Related

Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry

Sep 28, 2007

I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?


/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table

by Chuck King 20070928

***/

;WITH CTE

as

(

SELECT

ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID

,DischDate

,AdmDiagCode

,ID

,AdmDiagCodeDesc

,AdmittingDiagnosis

,AnyDx

,DischDx

,ECode

,IntOOS

,PrinDxAnesDesc

,PrinDxAnesInd

,PrinDxCode

,PrinDxCondDesc

,PrinDxCondInd

,PrinDxDesc

,PrinDxEqualsAdmDxYnu

,SecDx10AnesDesc

,SecDx10AnesInd

,SecDx10Code

,SecDx10CondDesc

,SecDx10CondInd

,SecDx10Description

,SecDx11AnesDesc

,SecDx11AnesInd

,SecDx11Code

,SecDx11CondDesc

,SecDx11CondInd

,SecDx11Description

,SecDx12AnesDesc

,SecDx12AnesInd

,SecDx12Code

,SecDx12CondDesc

,SecDx12CondInd

,SecDx12Description

,SecDx13AnesDesc

,SecDx13AnesInd

,SecDx13Code

,SecDx13CondDesc

,SecDx13CondInd

,SecDx13Description

,SecDx14AnesDesc

,SecDx14AnesInd

,SecDx14Code

,SecDx14CondDesc

,SecDx14CondInd

,SecDx14Description

,SecDx15Code

,SecDx15Description

,SecDx1AnesDesc

,SecDx1AnesInd

,SecDx1Code

,SecDx1CondDesc

,SecDx1CondInd

,SecDx1Description

,SecDx2AnesDesc

,SecDx2AnesInd

,SecDx2Code

,SecDx2CondDesc

,SecDx2CondInd

,SecDx2Description

,SecDx3AnesDesc

,SecDx3AnesInd

,SecDx3Code

,SecDx3CondDesc

,SecDx3CondInd

,SecDx3Description

,SecDx4AnesDesc

,SecDx4AnesInd

,SecDx4Code

,SecDx4CondDesc

,SecDx4CondInd

,SecDx4Description

,SecDx5AnesDesc

,SecDx5AnesInd

,SecDx5Code

,SecDx5CondDesc

,SecDx5CondInd

,SecDx5Description

,SecDx6AnesDesc

,SecDx6AnesInd

,SecDx6Code

,SecDx6CondDesc

,SecDx6CondInd

,SecDx6Description

,SecDx7AnesDesc

,SecDx7AnesInd

,SecDx7Code

,SecDx7CondDesc

,SecDx7CondInd

,SecDx7Description

,SecDx8AnesDesc

,SecDx8AnesInd

,SecDx8Code

,SecDx8CondDesc

,SecDx8CondInd

,SecDx8Description

,SecDx9AnesDesc

,SecDx9AnesInd

,SecDx9Code

,SecDx9CondDesc

,SecDx9CondInd

,SecDx9Description

,VisitTypeCode

,accountnumber

,DischVisitTypeCode

FROM ampfm.rpt_AdtVisitDiag

)

--Select * From CTE

Delete From CTE Where ROWID > 1

View 5 Replies View Related

Removing Characters In Field Grouping

Jun 15, 2007

Hello,



I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?






Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

View 2 Replies View Related

Removing Duplicate Value From One Column.

Feb 23, 2001

Hi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.

(I know distinct)
Thanks in advance.

Minesh.

View 2 Replies View Related

Removing Duplicate Records

Dec 5, 2007

Dear All

This is query i have written is giving following output.

FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:11
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-205H1-67XS23124148/6/2007 19:36:10
R1-220H1-66F433365348/6/2007 19:30:27
R1-220H1-66F433365348/6/2007 19:30:29
R1-400H1-64ER53436648/6/2007 19:24:23
R1-400H1-64ER53436648/6/2007 19:24:26
R1-408H1-65TE4626268/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:25:00


I want the output like this,it should take only Min Datecreated
FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-220H1-66F433365348/6/2007 19:30:27
R1-400H1-64ER53436648/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:24:23

View 2 Replies View Related

Removing Duplicate Records

Jul 11, 2006

I have a table that holds the following

1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM
1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM

for each 7530568 ordernumber there should only be one OESCHD status.

This is the query I'm using to insert the data sent to me.

INSERT INTO ORDER_EVENTS
SELECT d.division as division,
dt.orderNum as orderNum,
dt.poNum as poNum,
dt.statusCode as statusCode,
dt.statusChangeDate as statusChangeDate
FROM dt_Order_Events dt INNER JOIN
division d ON dt.division = d.divisionShort INNER JOIN
status s ON s.division = d.division AND s.statCode = dt.statusCode
WHERE directive <> 'C' AND
dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)

This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.

I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.

Any help would be apreciated.

View 1 Replies View Related

Removing Duplicate In Listbox

Apr 23, 2008

Hi There,

I am having a 1st listbox which is populating production lines, 2nd combobox to populate the production units. From here , the user will be able to select multiple production units from the combobox and hence populate the variable in listbox 3.

This is the query that I'm using to query on the variable table:
'SELECT DISTINCT PU_Id,Var_Id,Var_Desc from Variables
where PU_Id IN (@ProductionUnits)'

The problem now is that this would return all the variables for the selected production units and for those variables that have the same name, they would appear in the listbox as duplicates.

I wonder if there's any way to filter off or remove the duplicates in the listbox 3 by using sql query?

Thanks in advance for the help.

ksyong17

View 9 Replies View Related

Removing Duplicate Results With One Column Different?

Aug 9, 2012

I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.

Values in the table

Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154

The query I am using so far is:

Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'

What I need to see in the results

Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154

View 4 Replies View Related

Removing Duplicate Records With Criteria

Mar 9, 2015

I have regular work that requires me to extract a bunch of customer records from our database, and then remove duplicate address destinations (so we dont mail the same address more than once).

I can currently achieve this using a combination of my poor SQL skills and Excel, but it's really not working out for me, so looking for SQL wizardry necessary to do it just in SQL.

Relevant fields:
Member.AddressBarcode (This is a unique barcode (Text representation of a base-3 number) based on the customer address. So if there's more than one record in the pulled records with the same barcode, we then look at Member.MemberTypeID to determine whether to include this record in the results or discard it as a duplicate. Note that AddressBarcode may be blank if the mailing address couldn't be validated, if it is blank we don't discard it since there is no easy way to detect duplicate addresses without the barcode)

Member.MemberTypeID (This is the type of member account. We have 3 types - Single, Joint Primary, Joint Secondary, represented in this field by the numbers 1/2/3. This is also the order of preference of who to mail. So if there is a Joint Primary and Joint Secondary with the same mailing barcode, we want to discard the Joint Secondary from the results, so that the Joint Primary is the record we include in the results of who to mail.)

Member.ID (Unique numeric ID for each customer. Kind of irrelevant here, but it's a key)

So some pseudo code for what I'm trying to achieve is:

(Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 3 AND Member.AddressBarcode not in results of Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)

I suspect it requires some sort of join...

View 5 Replies View Related

Removing Duplicate Entries In SQL Field

Jan 3, 2007

Hi All,



Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.


Below is the SQL and the results from the returned page.


PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

View 1 Replies View Related

Removing Non-useful Records And Duplicate DB Objects. ( Purging)

Oct 9, 2007



hi All,
I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.

Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.

Plz help me reagrding these two problems.

Thanks in advance for the quick replies.
Mohit

View 1 Replies View Related

Removing Duplicate Delimited Values From A Single Column?

Jun 18, 2014

I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.

For example, this one column could contain something like this:

domainuser1;domainuser2;domainuser2;domainuser 3;

The need is to convert to this:

domainuser1;domainuser2;domainuser3;

I know that's a tall order.

View 1 Replies View Related

SQL Server 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

A Basic Question: Removing Duplicate Results From Max Function

Jun 10, 2006

Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir

View 4 Replies View Related

Transact SQL :: Removing Duplicate Values In ID Column Of Table

Oct 14, 2015

I need write a query for removing duplicates, for Example in my table I have columns

A_ID name id
1 sam 10
2 sam 10
3  sam 10
4 sam 10
5  ccc 15
6 ccc 15
7  ccc 15
8 fff 20
9 fff 20
10 fff 20

So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.

View 4 Replies View Related

Comparing Similar Tables - Removing Duplicate Or Repeated Data

Sep 30, 2007

It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.

I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.




Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item







No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.

As follows:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
A01
7680

12345
C01
20
A01
7680

54321
G10
1032
E15
1163

54321
G10
1032
F20
523

54321
G10
1032
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
Y15
3000

What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.

This is what I'm after:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
B01
0

12345
C01
20
C01
0

54321
G10
1032
E15
1163

54321
F20
0
F20
523

54321
H30
0
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
X10
0



Is this possible? If so, how?

I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.

-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:




Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER

FROM

(select item, bin, quantity
from BOOKED)q1
Left Join

(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join

(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item

Order By q1.item





I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...


Any suggestions?

View 7 Replies View Related

Filtering/Grouping To Remove Duplicate Values...

Oct 18, 2001

There'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

View 2 Replies View Related

Keep Duplicate With Highest Score Fuzzy Grouping

Jan 22, 2012

I have recently decided to dedupe my data but i am having a problem after running fuzzy grouping with the query on updating which duplicate to keep

_key_in is unique, _key_out is the duplicates so for example:

_key_in , _key_out , name , score , dedupe
1 , 1 , ron , 10 , purge
2 , 1 , ronn , 15 , keep
3 , 3 , john , 5 , keep
4 , 4 , matt , 15 , keep
5 , 4 , mat , 10 , purge
6 , 4 , matt , 15 , purge

I want to keep the _key_out with the higher score by setting the field de_dupe to 'keep' and the remainder to 'purge'. The score can also be the same within a duplicate so in the case it is the same i just need to keep one it doesnt matter which one. The query i have below nearly works but it marks duplicates with the same score as keep.

Code:
UPDATE b
SET b.dedupe_result = 'keep'
FROM
[BusinessListings].[dbo].[MongoOrganisationACTM1Destination] b
INNER JOIN

[Code] ....

View 2 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

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 Related

Removing Rows When A Textbox Value Is 0

Feb 2, 2008

Hi,

I am trying to remove rows of data when the value in a textbox in the row is 0.

My expression for a value of a textbox in my report layout is:


=Fields!salary_FTE.Value+Fields!Leave_FTE.Value

Could you please show me how I edit this expression so a row of data does not show when the value in this textbox is 0, or, should I create this filter in my query rather than in the report layout?

Thanks

View 5 Replies View Related

Removing Repetetive Rows

May 12, 2008

Hi, everyone
This is my data comming from Long StroredProcedure.In this Procedure will have 5 CTE's. I want to do with other function. i.e Removing Repetetive data form ACTION column. This ACTION column consist with BUY/SELL. Whenever two or More BUY/SELL i want to remove that one.

INPUT:
TABLE1
TIME ACTION
2008-05-09 19:33:07.657 SELL
2008-05-09 19:33:08.017 SELL
2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL
2008-05-09 19:33:09.063 SELL
2008-05-09 19:33:09.423 BUY
2008-05-09 19:33:10.093 BUY
2008-05-09 19:33:10.437 SELL
2008-05-09 19:33:10.797 SELL


OUTPUT:

TIME ACTION
2008-05-09 19:33:07.657 SELL

2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL

2008-05-09 19:33:09.423 BUY

2008-05-09 19:33:10.437 SELL


Please Help ME anyone..

View 6 Replies View Related

Removing Rows That Contains Long String

Mar 23, 2004

Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.

I should be able to write a program to do it, but I am wondering if I can do it in SQL.

View 2 Replies View Related

Removing Rows With Repeated Data

Sep 7, 2005

I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?

Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310

So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310

View 7 Replies View Related

Removing Columns And/or Rows On Export

Apr 3, 2007

I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?

View 5 Replies View Related

Sort Wrongly Removing Rows

Aug 7, 2007

I have a sort component which is (wrongly) reducing the number of rows* in my dataset.

NB "remove duplicates" is NOT ticked!

Why is this occuring?


*number displayed on the flow arrows at runtime

View 19 Replies View Related

Removing Rows From A SqlDataSource Before It Is Bound To A GridView?

Nov 5, 2006

I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?
(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)
Thanks.

View 9 Replies View Related

Transact SQL :: Removing Duplicates Rows With OVER Clause

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

Grouping Rows By Just The Date

Mar 2, 2001

I store data in a table using a column named InsertTimestamp which is a datetime format. I now want to report on rows by just the date. I have been able to do this by converting the datetime like such:
convert(varchar, inserttimestamp, 107)
This reurns the data correctly however is very ineffiecnet. DOes anyone know another easy way around this dilemna???

View 1 Replies View Related

Restricting The Rows And Grouping

Dec 14, 2004

Hi
I have a table strcutre as follows.This is like a many to many relationship between category and Article. Now i need to pick 3 recrord from each category which has a relavancy 1 sorted by Article date Desc. ie.. from the recent articles..
I can fire 4 diff queries and restrict it using top keyword..
Can i do this in a Single query or in a better way..can anyone help me plz......

Rgds
jinu

Category
---------
catID - PK
CatName

Article
-------
ArticleID - PK
Name
Date

Category_Article
----------------
ArticleID - Composite Key(ArticleID,CatId)
CatID
Relavancy

View 8 Replies View Related

Grouping Rows By CSV Column

Aug 24, 2013

I have a table with following sample data:

**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265

[code]....

I need to know :

1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)

Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved