CODEWITHSUNDEEP
sql-server
Mr_G
Mr_G

Reputation: 131

Find matching values in SQL based on multiple variables

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;

Upvotes: 0

Views: 357

Answers (1)

Stu
Stu

Reputation: 32589

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;

Upvotes: 1

Related Questions