Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql query to compare where rows have the same value in different columns

Posted on 2014-04-02
9
Medium Priority
?
7,818 Views
Last Modified: 2014-04-02
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!
0
Comment
Question by:flip4h
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39972642
how do we know the order of the rows?


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
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39972647
If we can make the assumption that order should be alphabetical based on the "old" column then try this...


SELECT o.old, o.new, n.new
  FROM yourtable o, yourtable n
 WHERE o.id = n.id AND o.new = n.old AND n.old > o.old

If there is some other criteria for determining which row came first, then please elaborate
0
 
LVL 32

Expert Comment

by:awking00
ID: 39972662
Is there any sort of change date available or perhaps some other indicator as to the order of change?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:flip4h
ID: 39972693
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39972713
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)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39972719
The last line should have had a Change# of 3
0
 

Author Comment

by:flip4h
ID: 39972749
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.
0
 

Author Comment

by:flip4h
ID: 39972803
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?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39972866
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question