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


ADVERTISEMENT

Filtering And/or Grouping Help

Jul 2, 2007

I might be looking at this from the completely wrong angle, making this way harder than it needs to be, so maybe some fresh perspectives will help.



Let's say I have a simple SELECT that returns the total number of Orders for a given date, grouped by Order Status and Order Date. So my columns returned are as follows:



Date - Status - NumOrders



Now, what I want to do is create another very simple table that has essentially 3 columns:



Date - ShippedOrders - TotalOrders

07/02 23 100



Now the Shipped Orders column is simply just one of the many status' that I have in my recordset, i.e.



07/02/2007 - Shipped - 23



My first thought would be to simply filter on my Shipped Orders column, however, it appears that SSRS only supports filters at the data region level, which wouldn't help my situation.



It feels like I'm missing something very basic here, so perhaps my brain is a bit cloudy from the weekend. I know I can achieve what I'm looking to do by easily manipulating the SQL, but I'm trying to increase my comfort level with SSRS. Any insight is appreciated.



Thanks.

View 2 Replies View Related

MDX Filtering And Grouping Data Within Subreports (help)

Aug 28, 2007

I have what at first site should be a simple reporting services issue but cannot resolve:-

I have a complex report comprising over 90 pages of various sections but when analysed in detail , 80% of it follows a simple pattern i.e. it comprises around 100 instances of the same sub-report bound to the same data source BUT grouped and filtered on different groupings and filter values.

The pattern is as follows:-


Each sub-report instance is bound to an SSAS cube which has fields L1, L2...L7

Each sub-report instance groups the data dynamically by one or more groups G1,G2..G7, the actual fields to be used being defined by the parent report (i.e. one instance may group by fields L7, L3, L4, another by L2,L3 etc)

Each sub-report instance has up to 7 filters defined (F1..F7) may filter the data i.e. the parent may pass a filter stipulating that L7="A", L3="B". To do this, the parent sets filter parameters F7="A" and F3="B"


The approach I have adopted is to set up parameters in the sub-report for dynamically grouping the data i.e. the
sub-report has 7 parameters defined G1..G7 and the parent report populates G1..G7 with the grouping breakdown required for any given instance. This works fine!!!!

However what I am trying to do is prevent each sub-report performing major round trips to the underlying cube for each sub-report instance by specifying the filter as part of the MDX query by defining the 7 filter parameters as query parameters. (I could of course not filter the query and place the filter in the report but this would almost certainly lead to performance issues). Instead what I am struggling to do is to create the MDX query in such a way that when a filter is not supplied by the parent, the MDX query does not filter the data.

Can anyone advise on this. I guess my question is more of an MDX question than a reporting services one.

View 1 Replies View Related

Filtering Duplicate ID's?

Jul 23, 2005

Hi, all:I'm having trouble with something that probably has a simple solution.I have linking tables that can list a particular MemberID multipletimes. Is there a way to run a query so that a specific ID will showup once?Here is an example of the tables I've set up --MemberTable:==MemberIDMemberName1Dave2John3MichaelFruitTable:==FruitIDFruitName1Apple2Orange3PearVeggieTable:==VeggieIDVeggieName1Carrot2Celery3Potato....and these linking tables --Members2Fruits:==MemberIDFruitID1213223132Members2Veggies:==MemberIDVeggieID1221222331This is the query I'm using to retrieve the ID's:SELECT distinct m.*, m2f.*, m2v.*FROM ((MemberTable m INNER JOIN Members2Fruits m2fON m.MemberID = m2f.MemberID)INNER JOIN Members2Veggies m2v ON m.MemberID = m2v.MemberID)WHERE ...By the way, I know of the GROUP BY clause, but it didn't work for me.Thanks for any help.J

View 2 Replies View Related

Filtering Duplicate Records

Jul 3, 2001

hi,

I am trying to fetch data from 2 tables, say TABLE1 and TABLE2, both of which got columns like id and num. Then i want all the rows from TABLE1 where id1=id2 and num1 != num2.
but it is showing all the rows for an id1 twice, if there are two records in TABLE2 with same id and num.
is there any way to filter those records without using the distinct keyword.

regards
Rajeev.

View 1 Replies View Related

Filtering On Duplicate Data

May 2, 2008

Hello,

I have a dataset which I would like to remove data from, but I can't seem to find out how to do this.

The dataset contains the following columns:

SCode, NIn, SIn, AIn, NOut, SOut, AOut and TagNumber.

I would like to remove data from the dataset when the following occurs:

("SC123", "NIn123", "s-in-323", "a-in-342", "NOut43", "s-out-231", "a-out-45", "tagnumber12")
("SC123", "NIn123", "s-in-xyz", "a-in-xws", NULL, NULL, NULL, "tagnumber12")

This is when NIn occurs with the same value more than once, and I would like to remove (or ignore, filter) the row when NOut, SOut and AOut are null.

I am new to SSIS and can't see how I could do this (although I'm sure it's possible).

If anyone could show me how I would appreciate it.

Thanks.

View 11 Replies View Related

Filtering Multiple Duplicate Record

May 2, 2014

I am new in SQL programming world, following is query that i had created

select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime

from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid

where netflowsources.LastTime NOT LIKE GETDATE()

which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example

nodeid value = a,a,a,b,b,c,c,c,c,d,d,d

But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.

View 9 Replies View Related

Filtering Out Duplicate Rows Based On Three Column

Nov 25, 2014

I want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query

;With Temp as (
SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,

[code]...

In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.

View 8 Replies View Related

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 View Related

Remove Duplicate

Apr 1, 2004

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

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

Remove Duplicate Entries

May 8, 2008

 

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>

 

 

 

View 6 Replies View Related

Remove Duplicate Record

Jul 27, 2000

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

View 1 Replies View Related

Remove Duplicate Data

Dec 16, 2004

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

View 4 Replies View Related

Need To Remove All Duplicate Records.

Feb 12, 2008

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

View 14 Replies View Related

Remove Duplicate Addresses For Each IDs

Mar 7, 2014

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] .....

View 5 Replies View Related

REMOVE DUPLICATE ROWS

Jul 23, 2005

Hi everyone.How can I get the unique row from a table which contains multiple rowsthat 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 singlerow.TIADiego

View 3 Replies View Related

Remove Duplicate Rows

Apr 10, 2006

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 Related

Faster Remove Duplicate SQL

Sep 5, 2007

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

View 5 Replies View Related

How To Remove Duplicate From CSV Using SSIS

May 22, 2007

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

View 7 Replies View Related

Remove Duplicate Value From One Cloumn Table

Aug 16, 2005

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 Related

Remove Duplicate Rows From Table

Mar 20, 2007

I 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

View 3 Replies View Related

T-SQL (SS2K8) :: Remove A Simple Duplicate

May 1, 2014

I 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.

View 4 Replies View Related

How To Remove Duplicate On Server Using Framer

Jun 5, 2014

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] ....

View 2 Replies View Related

How Can I Remove Duplicate Entries In A Sql Query?

Mar 2, 2006

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 Related

Remove Duplicate Rows From A Table

Jan 10, 2007



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

View 4 Replies View Related

Filtering Values

Apr 1, 2008

does anyone one know how to filter this 01/23/2008 to 2008. i just want the year and stored it to a column in my sql database.
 
thanks

View 3 Replies View Related

How To Remove Duplicate Records From Incoming Textfiles

Apr 16, 2007

Is there a way to check if duplicates exists in the incoming textfiles????

View 5 Replies View Related

Transact SQL :: Remove Duplicate Records But Keep At Least 1 Record

Dec 3, 2010

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?

View 9 Replies View Related

How To Remove Duplicate Rows From Full Join Query

Jan 26, 2008

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.
 

View 8 Replies View Related

Reporting Services :: How To Remove Duplicate Rows In SSRS 2008

Dec 21, 2011

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

Transact SQL :: How To Remove Duplicate Records Based On Date Column

Jul 28, 2015

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. "

View 4 Replies View Related

Filtering NULL Values From A Chart

Mar 18, 2008



I am facing some problems in displaying data in a chart on a report. Let me give you a background on the report


The report has 8 parameters, Industry,CType,PType, S#, ECode, Start Date, End Date and Trend.

The layout of the report has a table and a chart.

Both the table and the chart need to display Normalized Value ( count of ECode / count of PType that are closed in the date range selected) and the Trend ( where the trend can be Weekly, Monthly, Yearly or Quarterly).

Both the chart and the table should display the data for the entire date range i.e if the Trend is Month and the Date Range is Jan 2007 Jan 2008, then the table and the chart should display months from Jan 2007, Feb 2007 .... Jan 2008 irrespective of whether or not there are error codes present for that month.

In order to satisfy the point 4 mentioned above, we have created the main dataset in such a way that it would have one row for each day between Jan 2007 to Jan 2008. Any fields that do not have data corresponding to a date will come up as NULL in the dataset.



Now, we need to display a chart in the report which would be a Trend v/s the Normalized Value chart for each ECode. So, we have put in the Normalized value in the 'Value' field of the chart, Trend group in the 'Category' field of the chart and ECode in the 'Series' group of the chart. The chart displays fine except for one extra series for the NULL values in the Error Code ( the one in green below). Is there any way in which we can do away with this NULL series without changing the dataset? I tried using filters for the 'Series' but it doesn't work ( used filters like <>NULL, <> "" , <>Nothing, cstr(ECode) <> NULL/"" etc ).

View 4 Replies View Related







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