Duplicate Rows - SQL Server Forums

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

Duplicate Rows

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-06 : 09:49:32

Hi All

There is an existing report in my work and I need to modify it to not show duplicates of some records.

At some points in the report there are duplicate records showing but this is coming from one table in the schema where sometimes there is duplicate records anyway because the same product ID is used twice for testing or reporting etc.

In this new report I need to try and do something so the duplicates do not show in the report, although I do not need to physically remove them from the table.

I can run a query with group by to show a count of the records etc but I am not sure how I can process each record and programatically say "if this record is repeated do not show it".

I was thinking along the line of a cursor to process through each record but not sure of the actual statements I would need to produce.

The reporting tool is crystal reports.

Thanks for any advice

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-06 : 11:30:16

We need to know your used version of sql server and it would be nice to have
table structure
sample data
wanted output

In you case it is important to know which row on which condition is to show or not to show.

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

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-06 : 12:46:22

Data is not something i's like to share but I can try and use aliases. I am not in work just now but will post the details as soon as I get back in.

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-06 : 12:53:57

We don't want you to share structure and data from your real environment.
Look around in this forum there are lots of given sample data with no information about real data.
It is always the easier way to be less abstract with some samples so we can try to help you.

The used version is important for us to know because there are some new commands in 2005 and later we can use to handle duplicates.
Just do a SELECT @@VERSION

Greetings
Webfred

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

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-09-07 : 04:15:31

CREATE TABLE TestTable
(TT_id INT ,
tt_name varchar(50),
tt_datetime datetime)

INSERT INTO TestTable
(TT_ID,tt_name,tt_datetime)
SELECT 1,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 1,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 2,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 2,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'

----view your duplicate records
SELECT * FROM TestTable
----Get unique rows
WITH T1 AS (SELECT tt_id,tt_name,tt_datetime,ROW_NUMBER ( ) OVER ( PARTITION BY TT_ID, tt_name, tt_datetime ORDER BY TT_ID ) AS RNUM FROM TestTable )
SELECT tt_id,tt_name,tt_datetime FROM T1 WHERE RNUM = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources