How do I compare two rows in a SQL query?

[oslon]

CREATE TABLE tableName 
(
    abcd    VARCHAR(512),
    1.2.3.4    VARCHAR(512),
    120    VARCHAR(512),
    2024-02-27 09:59:03    VARCHAR(512),
        VARCHAR(512),
        VARCHAR(512),
        VARCHAR(512),
        VARCHAR(512),
        VARCHAR(512),
        VARCHAR(512)
);

INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('abcd', '1.2.3.4', '120', '2024-02-27 09:59:00', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('efgh', '9.10.11.12', '120', '2024-02-27 09:30:00', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('efgh', '9.10.11.12', '120', '2024-02-26 09:00:00', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('random', '1ANDOM', 'random', 'random', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('random', 'random', 'random', 'random', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('', '', '', '', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('', '', '', '', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('', '', '', '', '', '', '', '', '', '');
INSERT INTO tableName (abcd, 1.2.3.4, 120, 2024-02-27 09:59:03, , , , , , ) VALUES ('', '', '', '', '', '', '', '', '', '');
 

This is the table. What I want is this.

Find only the rows whose A's are same AND B's are same, C=120 and D's difference is less than 3 seconds.

How'd I do it? 

Rather than crude answers, I'd love some guidance (teach a man to fish rather than giving him a fish)

I am trying to learn SQL advanced concepts by these practical scenarios in my job where I get less opportunity to use SQL as I am not a data analyst but a support engineer. Any guidance to practice SQL in scenarios like this is also appreciated.

[Danishhafeez]

• Filter rows where A's are the same and B's are the same: You'll use the WHERE clause to filter rows based on these conditions.
• Filter rows where C=120: Again, use the WHERE clause to add this condition.
• Calculate the time difference between D values: You'll use date/time functions to calculate the time difference between D values in seconds. For example, in MySQL, you can use the TIMESTAMPDIFF() function.

Check if the time difference is less than 3 seconds: Add another condition in the WHERE clause to filter rows where the time difference is less than 3 seconds.

Here's how you can write the SQL query:

SELECT * FROM tableName t1 JOIN tableName t2 ON t1.abcd = t2.abcd AND t1.1.2.3.4 = t2.1.2.3.4 WHERE t1.C = '120' AND t2.C = '120' AND t1.2024-02-27 09:59:03 = '2024-02-27 09:59:03' AND t2.2024-02-27 09:59:03 = '2024-02-27 09:59:03' AND TIMESTAMPDIFF(SECOND, t1.2024-02-27 09:59:03, t2.2024-02-27 09:59:03) < 3;

In this query:

• tableName t1 and tableName t2 are aliases for the same table to represent two instances of the same table.
• JOIN tableName t2 ON t1.abcd = t2.abcd AND t1.1.2.3.4 = t2.1.2.3.4 ensures that we're joining rows where A's are the same and B's are the same.
• t1.C = '120' AND t2.C = '120' ensures that both instances have C = 120.
• TIMESTAMPDIFF(SECOND, t1.2024-02-27 09:59:03, t2.2024-02-27 09:59:03) < 3 calculates the time difference between the D values and checks if it's less than 3 seconds.

You can adjust this query according to your specific SQL dialect, as some databases might have slightly different syntax for date/time functions and aliases. Additionally, make sure to replace the placeholder column names like abcd, 1.2.3.4, etc., with the actual column names from your table.

 

Best Regard

Danish Hafeez | QA Assistant

ICTInnovations

[oslon]

t1.abcd=t2.abcd?

[Barand]

6 hours ago, oslon said:

I am trying to learn SQL advanced concepts

You should first learn the basic concepts, such as the difference between column names and column values and other attributes

[oslon]

Any books/courses you recommend? I've purchased 5 books on DBMS but they don't seem to teach SQL that much.

[Barand]

Perhaps https://dev.mysql.com/doc/refman/8.0/en/tutorial.html