Comparing column values in different rows.

5 pts.
Tags:
SQL
sample table: A B C D E F G 1 d 3 t j 0 e 2 0 l o e p d 1 d 3 t k 0 f Ok, so in the table, column A should be unique. As you can, see row 1 and row 3 have the same value in column A. However, they differ in columns E and G. A quick aside, this is a metadata table, where column A is the key back to the data, and columns B-G are descriptive statistics. Ok, so what I would like to do is cycle through and for every set of rows where column A is the same, delete all the values from any of the other columns when these values are not all the same. After doing this, the table above would look like this: A B C D E F G 1 d 3 t 0 2 0 l o e p d 1 d 3 t 0 Then I could do a select distinct so that column A is unique. Any help would be greatly appreciated.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

So you want to set b to NULL when MIN (b) <> MAX (b) and you GROUP BY a. Your table will end up like the result set of this query:

<pre>
SELECT a
, CASE WHEN min_b = max_b THEN min_b ELSE NULL END AS new_b
, CASE WHEN min_c = max_c THEN min_c ELSE NULL END AS new_c
, CASE WHEN min_d = max_d THEN min_d ELSE NULL END AS new_d
, CASE WHEN min_e = max_e THEN min_e ELSE NULL END AS new_e
, CASE WHEN min_f = max_f THEN min_f ELSE NULL END AS new_f
, CASE WHEN min_g = max_g THEN min_g ELSE NULL END AS new_g
FROM ( – Begin in-line view to calculate min_b, …, max_g
SELECT a
, MIN (b) AS min_b
, MAX (b) AS max_b
, MIN (c) AS min_c
, MAX (c) AS max_c
, MIN (d) AS min_d
, MAX (d) AS max_d
, MIN (e) AS min_e
, MAX (e) AS max_e
, MIN (f) AS min_f
, MAX (f) AS max_f
, MIN (g) AS min_g
, MAX (g) AS max_g
FROM table_x
GROUP BY a
) m – End in-line view to calculate min_b, …, max_g
ORDER BY a;
</pre>

To fix the original table, I wouldn’t do an UPDATE. I would save this result set in a new table, remove everythingfrom the original table, and then copy all rows from the new table. If that’s not an option for you, then you can UPDATE your table with the above as a correlated subquery, then remove duplicates, like you proposed.

NOTE: MIN and MAX don’t count NULLs. If any of your columns b,c,…g contain NULL, and you want that to be treated as any other value, then the solution is a little messier. You have to map the NULLs to some value that doesn’t actually appear in that column, or do a separate test for NULLs.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following