How to make SQL query to sort data of the same kind from two column in a table and select from the query.

Question

hello,

I have a table that has two column that contain similar data, but I want to check the two column col1 and col2, if the number of data in col1 is equal to number of data col2, make a temp list of col1 data that has lesser number of data in col2.
my table is like this.

col1 || col2
0001 || 0001
0002 || 0001
0003 || 0002
0004 || 0002
0005 || 0003
0006 || 0004
0007 || 0005

---

so I want to check col1 against col2, any data in col2 that is less than 2 will be listed on a temp list, from the temp list I will like to select any number of row.

Please anyone with Idea on how I can solve this.
Thanks.

Answer 1

Please also try:

;WITH cte as(SELECT DISTINCT col2,COUNT(col2) OVER(PARTITION BY col2) cc
             FROM #test)

SELECT t.col1,t.col2,
CASE WHEN cc>=2 THEN NULL ELSE t.col1 END temp
FROM #test t
LEFT JOIN cte c ON t.col1=c.col2

Regards
Echo

Answer 2

Please try:

CREATE TABLE #test(col1 INT,col2 INT)
INSERT INTO #test VALUES
(0001,0001),
(0002,0001),
(0003,0002),
(0004,0002),
(0005,0003),
(0006,0004),
(0007,0005)

SELECT *,CASE WHEN col1<col2 THEN col1 END col12,
CASE WHEN col2<col1 THEN col1 END col22
FROM #test

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

 CREATE TABLE #test(col1 INT,col2 INT)
 INSERT INTO #test VALUES
 (0001,0001),
 (0002,0001),
 (0003,0002),
 (0004,0002),
 (0005,0003),
 (0006,0004),
 (0007,0005)

 SELECT *,CASE WHEN col1<col2 THEN col1 END col12,
 CASE WHEN col2<col1 THEN col1 END col22
 FROM #test

I tied this, but it keep returning the exact copy of col1, instead of returning the difference.

Answer 4

Try this:

DECLARE @T TABLE (
    Col1 varchar(10),
    Col2 varchar(10)
);

INSERT INTO @T VALUES
('0001', '0000'),
('0002', '0001'),
('0003', '0001'),
('0004', '0002'),
('0005', '0002'),
('0006', '0003'),
('0007', '0004'),
('0008', '0005');

SELECT Col1, Col2, CASE WHEN CAST(Col1 AS int) - CAST(Col2 AS int) >= 2 THEN Col1 ELSE NULL END AS Temp
FROM @T;
GO

Answer 5

Please check:

     CREATE TABLE #test(col1 VARCHAR(15),col2 VARCHAR(15))  
     INSERT INTO #test VALUES  
     ('0001','0001'),  
     ('0002','0001'),  
     ('0003','0002'),  
     ('0004','0002'),  
     ('0005','0003'),  
     ('0006','0004'),  
     ('0007','0005')  
          
     SELECT *,  
     IIF(CAST(col1 AS INT)-CAST(col2 AS INT)>=2,col1,NULL) temp  
     FROM #test  

Output:

If you want to get all col1>col2 columns, please refer to:

 SELECT *,  
 IIF(CAST(col1 AS INT)>CAST(col2 AS INT),col1,NULL) temp  
 FROM #test  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".