Find matching values in SQL based on multiple variables

Question

UPDATED! I want to find rows in table_20220121 where two variables matches the table_20220121_b. Also, there could be several matches in table_20220121, but I only want the one with the biggest [Time since 1970]-value, that is, I want the output to be the row with number 88 and 1391 in table_20220121. Thanks!

drop table table_20220121
drop table table_20220121_b
drop table table_output

CREATE TABLE table_20220121
(
number  float,
IP   nvarchar(100),
[Time since 1970]   float,
[Timestamp]   nvarchar(100),
[Scroll Depth Threshold] float 
);
INSERT INTO table_20220121
VALUES
(84,'999.999.99.111',1635332371761,'2021-10-27T12:59:31.761+02:00',0.7),
(85,'999.999.99.111',1635332377747,'2021-10-27T12:59:37.747+02:00',0.7),
(86,'999.999.99.111',1635332387744,'2021-10-27T12:59:47.744+02:00',0.7),
(87,'999.999.99.111',1635332388600,'2021-10-27T12:59:48.600+02:00',0.8),
(88,'999.999.99.111',1635332397761,'2021-10-27T12:59:57.761+02:00',0.8),
(1387,'999.999.99.999',1640983016936,'2021-12-31T21:36:56.936+01:00',0.4),
(1388,'999.999.99.999',1640983030370,'2021-12-31T21:37:10.370+01:00',0.5),
(1389,'999.999.99.999',1640983055463,'2021-12-31T21:37:35.463+01:00',0.5),
(1390,'999.999.99.999',1640983100401,'2021-12-31T21:38:20.401+01:00',0.6),
(1391,'999.999.99.999',1640983115461,'2021-12-31T21:38:35.460+01:00',0.6)
;

CREATE TABLE table_20220121_b
(
IP   nvarchar(100),
[Scroll Depth Threshold] float 
);
INSERT INTO table_20220121_b
VALUES
('999.999.99.999',0.6),
('999.999.99.111',0.8)
;

select * from table_20220121;
select * from table_20220121_b;
go

Actually, this below is the ANSWER to my question. I have provided it here in the question section just for clarity:

with matches as (
   select *, Row_Number() over(partition by ip order by [Time since 1970] desc) rn
   from table_20220121 ta 
   where exists (
       select * from table_20220121_b tb 
       where ta.ip=tb.ip 
         and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
   )
)
select number, ip, [Time since 1970], Timestamp, [Scroll Depth Threshold]
/* into table_output */
from matches
where rn = 1;

Do not post answers in questions. You are welcome to answer your own question — Charlieface
– Charlieface, Commented Jan 22, 2022 at 21:45

Stu · Accepted Answer · 2022-01-22 10:09:34Z

1

The syntax you have tried to use is not supported by SQL Server (and you're using 'string literals' so if the syntax were supported, it would always return true); however you can just join the tables using multiple criteria and take the top n

select top(1) ta.*
/* into table_output */
from table_20220121 ta 
join table_20220121_b tb on ta.ip = tb.ip 
  and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
order by [Time since 1970] desc;

Alternatively you can join and filter for multiple rows using row_number to filter for the maximum value per group:

with matches as (
    select *, Row_Number() over(partition by ip order by [Time since 1970] desc) rn
    from table_20220121 ta 
    where exists (
        select * from table_20220121_b tb 
        where ta.ip=tb.ip 
          and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
    )
)
select number, ip, [Time since 1970], Timestamp, [Scroll Depth Threshold]
/* into table_output */
from matches
where rn = 1;

edited Jan 22, 2022 at 10:09

answered Jan 22, 2022 at 9:49

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr_G Over a year ago

Your answer indeed solves my question, but I need a more general answer. For example, the table table_20220121_b can have many rows, so it does not work when I test on my big table.

Stu Over a year ago

Ok I have updated above with an alternative method - your question was quite explicit about finding the one row, but assuming (you haven't made it clear) you want to do this for multiple IP addresses, you can use row number to partition by IP groups.

Mr_G Over a year ago

Thanks. I have updated by question now. Your code provided me with my desired output.

Collectives™ on Stack Overflow

Find matching values in SQL based on multiple variables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related