SQL Query to return which columns have different values given two rows

Question

I have one table like this:

id  status     time days ...
1   optimal     60  21
2   optimal     50  21
3   no solution 60  30
4   optimal     21  31
5   no solution 34  12
.
.
.

There are many more rows and columns. I need to make a query that will return which columns have different information, given two IDs. Rephrasing it, I'll provide two IDs, for example 1 and 5 and I need to know if these two rows have any columns with different values. In this case, the result should be something like:

id  status     time  days
1   optimal     60    21
5   no solution 34    12

If I provide IDs 1 and 2, for example, the result should be:

id   time 
1     60 
2     50

The output format doesn't need to be like this, it only needs to show clearly which columns are different and their values

Rule184 - does my solution solve the problem? – robinhoodjr Jul 22 '20 at 18:58 — robinhoodjr, Jul 22 '20 at 18:58

score 3 · Answer 1 · answered Jul 21 '20 at 22:19

3

I can tell you off the bat that processing this data in some sort of programming language will greatly help you out in terms of simplicity and readability for this type of solution, but here a thread of how it can be done in SQL.

Compare two rows and identify columns whose values are different

answered Jul 21 '20 at 22:19

Nik Srinivas

163
5

I'm using R, but I need the query anyways – afm Jul 21 '20 at 22:29

score 0 · Answer 2 · answered Jul 22 '20 at 03:28

If you are looking for the solution in R. Here is my solution:

df <- read.csv(file = "sf.csv", header = TRUE)

diff.eval <- function(first.id, second.id, eval.df) {
  res <- eval.df[c(first.id, second.id), ]
  cols <- colnames(eval.df)
  for (col in cols) {
    if (res[1, col] == res[2, col]) {
      res[, col] <- NULL
    }
  }
  return(res)
}

print(diff.eval(1, 5, df))
print(diff.eval(1, 2, df))

You just need to create a dataframe out of table. I just created a .csv for ease locally and used the data by importing into a dataframe.

SQL Query to return which columns have different values given two rows

2 Answers2