1

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:

Name SalePrice
NorthAmerica\US\Northeast\NewYork 8576
NorthAmerica\US\Northeast 2598
SouthAmerica\Brazil 1348
SouthAmerica\Chile\NorthEast 9726
SouthAmerica\Chile 4412
NorthAmerica\Canada\Ontario 3894
NorthAmerica\Canada 6321

Desired Output:

Name SalePrice
NorthAmerica\US\Northeast\NewYork 8576
SouthAmerica\Brazil 1348
SouthAmerica\Chile\NorthEast 9726
NorthAmerica\Canada\Ontario 3894

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);
forpas
  • 160,666
  • 10
  • 38
  • 76
2020db9
  • 153
  • 1
  • 9
  • 2
    a normalized relational structure would make the query very easy – nbk Jun 09 '22 at 12:51
  • @nbk I'm not sure what you mean – 2020db9 Jun 09 '22 at 12:52
  • 2
    see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and also storing hierarchical data in databases would be a good reading start – nbk Jun 09 '22 at 12:54
  • What if there are ties? For example, if you add "NorthAmerica\US\Northeast\Philadelphia", what would your expected outcome be? – George Mastros Jun 09 '22 at 15:36

1 Answers1

5

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.

forpas
  • 160,666
  • 10
  • 38
  • 76