From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Guidance needed on an alternative take on common prefix SQL |
Date: | 2019-08-07 01:01:54 |
Message-ID: | ab7e79db-b24b-0ef6-efa1-42e6440770c7@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2019-08-07 06:22:40 | Re: Sorting composite types |
Previous Message | Laura Smith | 2019-08-06 23:25:09 | Guidance needed on an alternative take on common prefix SQL |