I'm trying to figure out how to select a subset of rows from a table, with the longest unique string for each "parent" string. I'll provide table examples below and my code that didn't work.

Current Table:

Desired Output:

Originally, I thought I could apply some form of logic based off the number of backslashes (

example: SELECT * FROM TestTable WHERE Name LIKE '%\\%'

). However, this logic doesn't work because some names furthest branch may only have 1 backslash while others may have 3 .

Code to generate test table is below and any help/advice would be greatly appreciated

create table t1( 
[name] varchar(60), 
[saleprice] int  );


insert into t1 values ('NorthAmerica\US\Northeast\NewYork',8576);
insert into t1 values ('NorthAmerica\US\Northeast',2598);
insert into t1 values ('SouthAmerica\Brazil',1348);
insert into t1 values ('SouthAmerica\Chile\NorthEast',9726);
insert into t1 values ('SouthAmerica\Chile',4412);
insert into t1 values ('NorthAmerica\Canada\Ontario',3894);
insert into t1 values ('NorthAmerica\Canada',6321);

CodePudding user response：

Use the operator LIKE with NOT EXISTS:

SELECT t1.*
FROM t1
WHERE NOT EXISTS (
  SELECT 1
  FROM t1 AS t2
  WHERE t2.name LIKE t1.name   '_%'
);

See the demo.