On Wednesday, August 7, 2019 2:01 AM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote: > On 8/6/19 6:25 PM, Laura Smith wrote: > > > Hi, > > I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match"). > > I'm in need of a bit of guidance on how best to implement an alternative take. Frankly I don't quite know where to start but I'm guessing it will probably involve CTEs, which is an area I'm very weak on. > > So, without further ado, here's the scenario: > > Given an SQL filtering query output that includes the following column: > > 87973891 > > 87973970 > > 87973971 > > 87973972 > > 87973973 > > 87973975 > > 87973976 > > 87973977 > > 87973978 > > 87973979 > > 8797400 > > The final output should be further filtered down to: > > 87973891 > > 8797397 > > 8797400 > > i.e. if $last_digit is present 0–9 inclusive, recursively filter until the remaining string is all the same (i.e. in this case, when $last_digit[0-9] is removed, 8797397 is the same). > > So, coming back to the example above: > > 8797397[0-9] is present > > so the "nearest common" I would be looking for is 8797397 because once [0-9] is removed, the 7 is the same on the preceeding digit. > > The other two rows ( 87973891 and 8797400) are left untouched because $last_digit is not present in [0-9]. > > Hope this question makes sense ! > > Laura > > Hows this? > > select distinct > case cc > when 1 then num > else left(num,-1) > end > from ( > select > num, > (select count(*) as cc from numbers n2 where left(n2.num, -1) = left(numbers.num, -1)) > from numbers > ) as tmpx ; > > -Andy Hi Andy, That looks supremely clever ! I have just done a quick test and looks like it works as intended. Will do some more thorough testing with a larger dataset in due course. Thank you very much indeed Laura
