SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Sql query that does not return duplicate rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbeginner123
Starting Member

9 Posts

Posted - 08/10/2012 :  09:18:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/10/2012 :  09:52:21  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlbeginner123
Starting Member

9 Posts

Posted - 08/10/2012 :  15:20:33  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/10/2012 :  15:36:52  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000