Sql query that does not return duplicate rows

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Sql query that does not return duplicate rows

Author

Topic

sqlbeginner123
Starting Member

9 Posts

Posted - 2012-08-10 : 09:18:15

I have a sql query I am running and when the query runs it is returning some duplicate rows. How can I remove the duplicate rows. I have tried using the distinct and unique function but I get return errors.

This is my script:

right(replicate('0', 10)+replace(column_manme, ' ',''),10)) +
case
when voided = -1 then 'V'
when void = -1 then 'H'

The column is returning two voids a voided row and a void row. I actually only need one of the rows returned. I created the case statemnent just to sepearte out the two voids to tell them apart but I only need one void to return. How can I do this?

Any help woudl be greatly appreciated.

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 09:52:21

In SQL 2000, you can use GROUP BY and/or MAX, or a WHERE clause depending on your data. For example, may be one of these?


-- 1 
WHERE
	voided = -1

-- 2

SELECT
MAX(
	right(replicate('0', 10)+replace(column_manme, ' ',''),10)) +
	case
	when voided = -1 then 'V'
	when void = -1 then 'H'	END)
....
...
GROUP BY
	right(replicate('0', 10)+replace(column_manme, ' ',''),10))


--- 3
SELECT
right(replicate('0', 10)+replace(column_manme, ' ',''),10)) +
case
when voided = -1 then 'V'
when void = -1 then 'H' END
...
...
GROUP BY
right(replicate('0', 10)+replace(column_manme, ' ',''),10)) +
case
when voided = -1 then 'V'
when void = -1 then 'H' END

For #2 and #3, if there are other columns in the select list, you will either need to include them in the group by clause or wrap in an aggregate function such as MAX.

sqlbeginner123
Starting Member

9 Posts

Posted - 2012-08-10 : 15:20:33

This is the entire script:

Select
(column_anme1) +
(right(replicate('0', 10)+replace(column_name2, ' ',''),10)) +
(right(replicate('0', 10)+replace(ABS(Column_name3), '.',''),10)) +
(REPLACE(CONVERT(VARCHAR, Column_name4, 1), '/', '')) +
(right(((replace((Upper(Column_name5)),'','')) + (replace((Upper(Column_name6)),'','')))+ replicate(' ', 40 - LEN((Column_name5) + (Column_name6))),40))+
case
when voided = -1 then 'V'
when void = -1 then 'H'
else 'I' end
FROM trans tr
left outer join column c on (Column_name = x)
left outer join Column b on (Column_name = xx)
where 1 = 1
and x = 2
and xx = b.hMy
order by Column_Name2

When I run this script the query returned is like this:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxJACK I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJILL I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX050112JASON V
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX051712JASON V
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJAMES I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXMATTHEW I

I only need 1 of the "V" lines not both. What sql function will allow me to exclude one of the "V" rows?

Thank you in advance for any assistance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 15:36:52

quote:
Originally posted by sqlbeginner123

This is the entire script:

Select
(column_anme1) +
(right(replicate('0', 10)+replace(column_name2, ' ',''),10)) +
(right(replicate('0', 10)+replace(ABS(Column_name3), '.',''),10)) +
(REPLACE(CONVERT(VARCHAR, Column_name4, 1), '/', '')) +
(right(((replace((Upper(Column_name5)),'','')) + (replace((Upper(Column_name6)),'','')))+ replicate(' ', 40 - LEN((Column_name5) + (Column_name6))),40))+
case
when voided = -1 then 'V'
when void = -1 then 'H'
else 'I' end
FROM trans tr
left outer join column c on (Column_name = x)
left outer join Column b on (Column_name = xx)
where 1 = 1
and x = 2
and xx = b.hMy
order by Column_Name2

When I run this script the query returned is like this:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxJACK I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJILL I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX050112JASON V
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX051712JASON V
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJAMES I
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXMATTHEW I

I only need 1 of the "V" lines not both. What sql function will allow me to exclude one of the "V" rows?

Thank you in advance for any assistance.

By "V" lines, I assume you mean where the last column is V. If the column voided is in the table trans, all you need to do is to add another condition to the WHERE clause like shown below

SELECT (column_anme1) +(
           RIGHT(REPLICATE('0', 10) + REPLACE(column_name2, ' ', ''), 10)
       ) +(
           RIGHT(
               REPLICATE('0', 10) + REPLACE(ABS(Column_name3), '.', ''),
               10
           )
       ) +(REPLACE(CONVERT(VARCHAR, Column_name4, 1), '/', '')) +(
           RIGHT(
               (
                   (REPLACE((UPPER(Column_name5)), '', '')) + (REPLACE((UPPER(Column_name6)), '', ''))
               ) + REPLICATE(' ', 40 - LEN((Column_name5) + (Column_name6))),
               40
           )
       ) +
       CASE 
            WHEN voided = -1 THEN 'V'
            WHEN void = -1 THEN 'H'
            ELSE 'I'
       END
FROM   trans tr
       LEFT OUTER JOIN COLUMN c
            ON  (Column_name = x)
       LEFT OUTER JOIN COLUMN b
            ON  (Column_name = xx)
WHERE  1 = 1
       AND x = 2
       AND xx = b.hMy
       AND voided = -1
ORDER BY
       Column_Name2

If voided is in one of the other tables, you can still do that and, you don't need to do this, but you can change the join on that table to an INNER JOIN instead of LEFT JOIN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources