Removing Duplicate Results from this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing Duplicate Results from this query

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 06:58:52

Hey I have the following query

SELECT DISTINCT [content].content_id, [content].content_title, taxonomy_item_tbl.taxonomy_item_date_modified
FROM taxonomy_item_tbl INNER JOIN
[content] ON [content].content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE ([content].content_title LIKE '%%')
GROUP BY [content].content_id, [content].content_title, taxonomy_item_tbl.taxonomy_item_date_modified
ORDER BY [content].content_title

And I get back some duplicates which I am trying to avoid.

If i remove taxonomy_item_tbl.taxonomy_item_date_modified from the select and group it does remove the duplicates but I need this data column.

So just wondering how I can keep my query but remove duplicates

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-28 : 07:01:26

Please give table structure and sample data and wanted output so we don't have to guess...

No, you're never too old to Yak'n'Roll if you're too young to die.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:02:12

can you show the output of this query for a couple of rows, which demonstrate the duplication you are talking about?

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 07:02:30

8900 A Research Study into Consumers’ Attitudes to Food Labelling (2009) 12/1/2009 10:29:38 AM
8232 A Surveillance Study of E.coli O157:H7 and Enterobacteriaceae in Irish Retail Minced Beef and Beef Burgers (2002) 4/23/2009 3:55:27 PM
8494 A Surveillance Study on Levels of Artificial Colours and Sweeteners in Irish Retail Products 6/23/2009 6:22:23 PM
9682 Accuracy of Nutrition Labelling of Pre-Packaged Food in Ireland 7/14/2010 12:41:49 PM
9682 Accuracy of Nutrition Labelling of Pre-Packaged Food in Ireland 7/14/2010 12:41:49 PM
8408 Acrylamide 6/8/2009 11:20:09 AM
8184 Acute Gastroenteritis in Ireland, North and South - A Telephone Survey (2003) 4/24/2009 10:14:21 AM
1886 Advice for Caterers on the Country of Origin Beef Labelling Requirements 1/27/2009 5:26:25 PM
9304 Allergens 1/11/2010 10:50:33 AM
9304 Allergens 1/11/2010 10:50:33 AM

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:17:56

All seems to be distinct ? Show us an example of the duplicated rows which come up as a result of the above query?

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 07:24:50

Thats I thought to but as u can see in my previous post duplicates still return i.e last 2 rows

9304 Allergens 1/11/2010 10:50:33 AM
9304 Allergens 1/11/2010 10:50:33 AM

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 07:31:13

ah just noticed the full dates for those 2 duplicates are

2010-01-11 10:50:33.310
2010-01-11 10:50:33.327

so thats whats making them unique, how do I get around this ?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:31:40

run the same query specifically for content_id=9304 without Distinct

let us know how many rows it returns in this case?

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 07:34:02

SELECT [content].content_id, [content].content_title, taxonomy_item_tbl.taxonomy_item_date_modified
FROM taxonomy_item_tbl INNER JOIN
[content] ON [content].content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE ([content].content_title LIKE '%%') and content_id =9304
GROUP BY [content].content_id, [content].content_title, taxonomy_item_tbl.taxonomy_item_date_modified
ORDER BY [content].content_title

Returns

9304 Allergens 2010-01-11 10:50:33.310
9304 Allergens 2010-01-11 10:50:33.327

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:37:48

ah just noticed the full dates for those 2 duplicates are

2010-01-11 10:50:33.310
2010-01-11 10:50:33.327

so thats whats making them unique, how do I get around this ?

I suspected it as a Data issue and was after this :D

Is it compulsory to have the time portion in your result set?

There are many ways to cater this .. one of which is by replacing the specific column in your original query with this one

cast (convert(varchar(19),tableName.ColumnName)as datetime)

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 07:41:08

No It isnt

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:43:26

Ok if it is not then use this query:

SELECT DISTINCT
[content].content_id
, [content].content_title
, cast(convert(varchar(8),taxonomy_item_tbl.taxonomy_item_date_modified,112) as datetime) as taxonomy_item_date_modified
FROM taxonomy_item_tbl INNER JOIN
[content] ON [content].content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE ([content].content_title LIKE '%%')
GROUP BY [content].content_id, [content].content_title, taxonomy_item_tbl.taxonomy_item_date_modified
ORDER BY [content].content_title

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-01-28 : 09:51:18

Thanks man worked

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 10:03:19

yrw :)O

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-28 : 11:46:39

You should really avoid casting to unnecessary data types (like date to string and back to date) when you can just do simple date math:

DATEADD(DAY, DATEDIFF(DAY, 0, taxonomy_item_tbl.taxonomy_item_date_modified), 0)

Or, even better if using SQL 2008:

CAST(taxonomy_item_tbl.taxonomy_item_date_modified AS DATE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources