On 8/7/19 3:36 AM, Laura Smith wrote:
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
If the target field is really an integer type and you have lots of rows
you might be better off with arithmetic functions.
create table short as select id/10 as base, array_agg(mod(id,10)) as
odds from head group by base;
select * from short;
base | odds
---------+---------------------
879740 | {0}
8797389 | {1}
8797397 | {0,1,2,3,5,6,7,8,9}
(3 rows)
select case when array_length(odds,1) = 1 then 10*base + odds[1] else
base end from short;
base
----------
8797400
87973891
8797397
(3 rows)
