89614

How to compare two rows or get the fields which value is not match with the rows that compared?

I'm trying to get the fields which value is not match base on the compared rows. It's hard to explain so I'll put the sample table and its results.

<strong>Table:</strong> orders

| seqid | orderId | taskId | field1 | field2 | field3 | field4 | +---------+-----------+----------+----------+----------+----------+----------+ | 1 | 1 | 1 | a | b | c | d | | 2 | 1 | 2 | a | b | c | d | | 3 | 2 | 1 | a | b | c | d | | 4 | 2 | 2 | a | c | c | c | | 5 | 3 | 1 | a | a | a | a |

<strong>Results:</strong>

| OrderId | FieldName | Error | +-----------+-------------+---------+ | 2 | field2 | 1 | | 2 | field4 | 1 |

Here you can see that the orderId will be compared using its taskId. From field1 to field4 are the fields to be check if their values are not match. If so, then get the field name. The column Error is not a priority, you can disregard it. If the orderId has only one task then it will not be compared.

My first plan is to use two query that will separate the taskId then Java will do the rest. But if possible with just a query that would be great. Even though I'm not knowledgeable with stored procedure, if its the only way then let me show and I'll check on it.

I don't have any working query yet or even get a result as close to what I wanted. Any answer will be appreciate. Thanks!

<strong>UPDATED:</strong>

I guess the results is difficult to answer. I'll post another result which is I think much easy to solve.

<strong>Results2</strong>

| orderId | field1 | field2 | field3 | field4 | +-----------+----------+---------+---------+----------+ | 1 | 0 | 0 | 0 | 0 | | 2 | 0 | 1 | 0 | 1 |

For the second results I need to know if the field has unmatched value if yes it will display 1. The process of this results is to compare the two rows which is the Task = 1 and Task = 2 with same orderId. I will filter the fields which have unmatched value in Java.

I hope someone here can answer it even the second results. If you can answer the first results, that would be a great help.

Answer1:

SELECT orderId, CASE MIN(field1) WHEN MAX(field1) THEN 0 ELSE 1 END AS field1, CASE MIN(field2) WHEN MAX(field2) THEN 0 ELSE 1 END AS field2, ... FROM atable GROUP BY orderId

Answer2:

Try this:

select orderid from orders group by orderid having count(*) > 1 and min(field1) <> max(field1) and min(field2) <> max(field2) ...

Recommend

  • How to maintain uniqueness during SQL Join within Access-VBA function?
  • How to use generics properly for a Holder
  • How could we use ibpy for editing orders?
  • Joining multiple tables in bigquery
  • Is it possible to display more than one item in a cell with a foreach condition?
  • Describe relationship in maximo 7.5
  • Getting CDATA XML Section Using Linq
  • How to create a TFS alert for changes to the items' Stack Rank field
  • Get column names and values in for loop PHP
  • Scala Play form validation: different forms for one case class - is it possible?
  • Cannot access sitecore item field via API
  • Is playing sound in Javascript performance heavy?
  • Groovy: Unexpected token “:”
  • Replace value with Factor in r data.table
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Recording logins for password protected directories
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Insert into database using onclick function
  • Retrieving value from sql ExecuteScalar()
  • Splitting given String into two variables - php
  • Volusion's generic SQL folder, functionality
  • Cross-Platform Protobuf Serialization
  • Check if a string to interpolate provides expected placeholders
  • Knitr HTML Loop - Some HTML output, some R output
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Convert array of 8 bytes to signed long in C++
  • Is there a mandatory requirement to switch app.yaml?
  • How to format a variable of double type
  • KeystoneJS: Relationships in Admin UI not updating
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • coudnt use logback because of log4j
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • JaxB to read class hierarchy
  • Checking variable from a different class in C#
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices