| Author |
 Topic  |
|
|
sqlbeginner123
Starting Member
9 Posts |
 Posted - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/10/2012 : 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' ENDFor #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 - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/10/2012 : 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 belowSELECT (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_Name2If 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. |
|
|
| |
Topic |
|
|
|
Sql query that does not return duplicate rows
