flip4h
asked on
Sql query to compare where rows have the same value in different columns
Have a sample table as follows:
ID Old New
123 a b
123 b a
456 a b
456 b c
Would like to write a query to select where an ID has the same Old value as it does a New value.
Basically, looking for where the old value was changed and then changed back to the current value, and what those 2 values would be.
So the result set I want back is:
ID Old New Newest
123 a b a
I would not want to get any of the 456 IDs back because that went from a to b to c, not back to a again.
Hope this is enough information.
Thanks!
ID Old New
123 a b
123 b a
456 a b
456 b c
Would like to write a query to select where an ID has the same Old value as it does a New value.
Basically, looking for where the old value was changed and then changed back to the current value, and what those 2 values would be.
So the result set I want back is:
ID Old New Newest
123 a b a
I would not want to get any of the 456 IDs back because that went from a to b to c, not back to a again.
Hope this is enough information.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is there any sort of change date available or perhaps some other indicator as to the order of change?
ASKER
Thanks for looking into this.
Yes, there would be a change date associated, I created a dummy "table" for this example. Just think the order it is in is the indicator of change, added a Change# as the indicator:
So table could be this:
ID Change# Old New
123 1 a b
123 2 b a
456 1 a b
456 2 b c
Yes, there would be a change date associated, I created a dummy "table" for this example. Just think the order it is in is the indicator of change, added a Change# as the indicator:
So table could be this:
ID Change# Old New
123 1 a b
123 2 b a
456 1 a b
456 2 b c
Is it possible for it to be something like this?
123 1 a b
123 2 b a
456 1 a b
456 2 b c
456 2 c b (or a)
123 1 a b
123 2 b a
456 1 a b
456 2 b c
456 2 c b (or a)
The last line should have had a Change# of 3
ASKER
Yes that scenario would be possible.
A little more background on scenario
This table is an audit table, when a change is made, a new record is created. So could have numerous change#. Trying to find out if an ID is being changed and then changed back. So it would not matter what change# it is, just whether or not for the same ID if that record had an old value that matched the new value (but going at least in the order of the change#).
So in the scenario you just gave, no matter if that chnage# 3 has a or b as its new value, it would need to show.
Because
Change# 1 has a as old value
Change# 2 has b as old value
So if Change# 3 has a as new value, then because 3 > 1 and change# 1 old = change# 3 new, then it would show
If Change# 3 has b as new value, then because 3 > 2 and change# 2 old = change# 3 new, then it would show
Hope this explains it more!
Thanks again.
A little more background on scenario
This table is an audit table, when a change is made, a new record is created. So could have numerous change#. Trying to find out if an ID is being changed and then changed back. So it would not matter what change# it is, just whether or not for the same ID if that record had an old value that matched the new value (but going at least in the order of the change#).
So in the scenario you just gave, no matter if that chnage# 3 has a or b as its new value, it would need to show.
Because
Change# 1 has a as old value
Change# 2 has b as old value
So if Change# 3 has a as new value, then because 3 > 1 and change# 1 old = change# 3 new, then it would show
If Change# 3 has b as new value, then because 3 > 2 and change# 2 old = change# 3 new, then it would show
Hope this explains it more!
Thanks again.
ASKER
Which I believe using sdstuber's query, and modifying it to look like:
SELECT o.old, o.new, n.new
FROM yourtable o, yourtable n
WHERE o.task_id = n.task_id AND o.old_value = n.new_value and o.change# < n.change#
Should give me exactly what I need then, correct?
SELECT o.old, o.new, n.new
FROM yourtable o, yourtable n
WHERE o.task_id = n.task_id AND o.old_value = n.new_value and o.change# < n.change#
Should give me exactly what I need then, correct?
yes
the only thing missing from my query was the sorting rule, so I made up my own.
If you have a change# to use for sorting, then, as noted above, use that
the only thing missing from my query was the sorting rule, so I made up my own.
If you have a change# to use for sorting, then, as noted above, use that
123 a b
123 b a
could just as easily be
123 b a
123 a b
we have no way of knowing by looking at your data whether it changed from A to B to A
or from B to A to B