How do I compare two columns in the same table?
SELECT * FROM FOO WHERE C1 = C4
should work. Does it not?
If not, are they the same data type and length? You may need to convert.
I don't know about WebSql, but I've seen some db systems that refuse to match if one is a varchar(5) and the other is a varchar(10) even though they hold the same value.
In those systems you have to use something like
Convert(varchar, 10, FieldName)
to get a match.
Comparing 2 Columns in same table
select
count(*) as rows_checked,
sum(col = col2) as rows_matching,
sum(col != col2) as rows_different
from table
Note the elegant use of sum(condition)
.
This works because in mysql true
is 1
and false
is 0
. Summing these counts the number of times the condition is true
. It's much more elegant than case when condition then 1 else 0 end
, which is the SQL equivalent of coding if (condition) return true else return false;
instead of simply return condition;
.
Comparing values in two columns in same table and creating new column
This is a little tricky because when there is only one row, then you want to keep the original ordering. One method is aggregation with union all
:
select least(sender, receiver) as sender, greatest(sender, receiver) as receiver, 1 as exchanged
from t
group by least(sender, receiver), greatest(sender, receiver)
having count(*) = 2
union all
select sender, receiver, 0
from t
where not exists (select 1
from t t2
where t2.receiver = t.sender and t2.sender = t2.receiver
);
How to compare two columns in the same table, and get a distinct one
You can first fetch the default key,values pairs, excluding the ones that have a value for the corresponindg user and then use UNION
and fetch the key,values pairs for the user.
A query using NOT IN
and a subquery:
SELECT * FROM `options` WHERE `user` IS NULL
AND `key` NOT IN
(SELECT `key` FROM `options` WHERE `user`='John')
UNION ALL
SELECT * FROM `options` WHERE `user`='John'
The same query using JOIN
SELECT o.* FROM `options` o
LEFT JOIN
(SELECT `key` FROM `options` WHERE `user`='John') o2
ON o.key=o2.key WHERE o2.key IS NULL AND o.user IS NULL
UNION ALL
SELECT * FROM `options` WHERE `user`='John'
SQL query to compare multiple columns in same table in oracle
You can use MINUS.
if no_data then both are the same, if there are some records - mean that there is a difference between
create table emp as select * from hr.employees;
insert into emp select employee_id+1000, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id,
decode(department_id ,30,70, department_id)
from hr.employees;
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
from emp where employee_id <= 1000
minus
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
from emp where employee_id > 1000;
But you have to list all columns, because if you have eg different dates or ids - they will be compared too. But it's easier to list columns in SELECT clause then write for everyone WHERE condition.
Maybe it will help.
-- or if different tables and want to compare all cols simply do
drop table emp;
create table emp as select * from hr.employees;
create table emp2 as
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id,
decode(department_id ,30,70, department_id) department_id
from hr.employees;
select * from emp
minus
select * from emp2;
---- ADD DATE CRITERIA
-- yes, you can add date criteria and using analytical functions check which
-- is newer and which is
older and then compare one to another. like below:
drop table emp;
create table emp as select * from hr.employees;
insert into emp
select
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date+1,
job_id,
salary,
commission_pct,
manager_id,
decode(department_id ,30,70, department_id)
from hr.employees;
with data as --- thanks to WITH you retrieve data only once
(select employee_id, first_name, last_name, email, phone_number,
hire_date,
row_number() over(partition by employee_id order by hire_date desc) rn -- distinguish newer and older record,
job_id, salary, commission_pct, manager_id, department_id
from emp)
select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 1
MIUNUS--- find the differences
select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 2;
Comparing same columns in the same table
Seems a self join would work...
SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN table2 T2
on T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date
or an exists (faster but access to only T1 data)
SELECT T1.*
FROM Table1 T1
WHERE Exists (Select 1
from table1 T2
where T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date)
SQL Comparing Two Columns in same table and update in another table
As I understand you have something like linked lists and for each linked list you want to get min and max id.
If you are using SQL Server this should work straight away, otherwise I think you will be able to apply the same method to other rdbms.
Table declaration and test values insert:
CREATE TABLE #t
(
Col1 int,
Col2 int
)
INSERT INTO #t VALUES (100, 200), (200, 300 ), (300, 400), (400, 500), (700, 900)
And the actual code:
SELECT MIN(Col1) AS Col, MAX(Col2) AS New FROM
(
SELECT SUM(SeqIDStart) OVER (ORDER BY Col1 ASC) AS SeqID, *
FROM (
SELECT
CASE WHEN LAG(B.Col1, 0, NULL) OVER (ORDER BY a.Col1 ASC) IS NULL THEN 1 ELSE 0 END AS SeqIDStart,
a.col1 AS Col1, a.Col2 AS Col2, B.Col1 AS adjsCol
FROM
#t a
LEFT JOIN
#t b
ON a.col1 = b.col2
WHERE a.Col1 IS NOT NULL
) a
) b
GROUP BY SeqID
I hope this will help
How to compare two row in same table and return the data in response using stored procedure
You need to unpivot all the columns, then join each row to every other.
You can either pivot everything manually using CROSS APPLY (VALUES
SELECT
aId = a.id,
bId = b.id,
v.columnName,
v.value1,
v.value2
FROM @t a
JOIN @t b
ON a.id < b.id
-- alternatively
-- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;
The use of WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2)
means that nulls will get taken into account properly.
Or you can use SELECT t.* FOR JSON
and unpivot using OPENJSON
WITH allValues AS (
SELECT
t.id,
j2.[key],
j2.value,
j2.type
FROM @t t
CROSS APPLY (
SELECT t.*
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
) j1(json)
CROSS APPLY OPENJSON(j1.json) j2
WHERE j2.[key] <> 'id'
)
SELECT
aId = a.id,
bId = b.id,
columnName = a.[key],
value1 = a.value,
value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
AND a.id < b.id
-- alternatively
-- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
OR a.value <> b.value
FOR JSON PATH;
db<>fiddle
SQL Fiddle of actual data
Related Topics
Copy and Insert to Same Table No Duplication and With Minor Changes to Value
How to Minus Current and Previous Value in SQL Server
How to Subtract One Day from Current Date Then Convert to String in Hive
How to Insert an Image in Sqlite Database(Table)
Daily Report by Date With Mssql for Mutiple Column
Comparing Two Count Results Using SQL
Sql Query to Select from 1 Hour Ago
Calculate Total Working Hour of Employee in SQL With Only 1 Column
Retrieving the Last Record in Each Group - MySQL
How to Add Leading Zero When Number Is Less Than 10
Grouping But With Keeping All Non-Null Values
How to Change Date Format in .Net Rdlc Report
How to Select Oldest Date from MySQL
Oracle Query to SQL Server Query
Convert the Students Marks into Respective Grades
Group MySQL Query by 15 Min Intervals
How to Select All Records from One Table That Do Not Exist in Another Table
Could Not Load File or Assembly Error in .Net Standard 2.0 Class Library