select * from  (
select hub_rec.pos_record_number, hub_pos.base_record_number, hub_pos.pos_number,
   hub_rec.pos_record_id, lnk.pos_id, row_number() over (partition by base_record_number
	order by case when base_record_number=pos_record_number then 0 else 
	substring(pos_record_number from '\d+$')::int  end desc) rn
  FROM templ.lnk_pos_record lnk
              JOIN templ.hub_pos_record hub_rec ON hub_rec.pos_record_id = lnk.pos_record_id
              JOIN templ.hub_pos hub_pos ON hub_pos.pos_id = lnk.pos_id) ch where rn = 1[SRC PLSQL]

"Subquery Scan on ch  (cost=125673.76..142512.26 rows=1981 width=108) (actual time=8983.971..13651.526 rows=335605 loops=1)"
"  Filter: (ch.rn = 1)"
"  Rows Removed by Filter: 60595"
"  ->  WindowAgg  (cost=125673.76..137559.76 rows=396200 width=100) (actual time=8983.968..13578.862 rows=396200 loops=1)"
"        ->  Sort  (cost=125673.76..126664.26 rows=396200 width=100) (actual time=8983.951..13037.732 rows=396200 loops=1)"
"              Sort Key: hub_pos.base_record_number, (CASE WHEN ((hub_pos.base_record_number)::text = (hub_rec.pos_record_number)::text) THEN 0 ELSE ("substring"((hub_rec.pos_record_number)::text, '\d+$'::text))::integer END) DESC"
"              Sort Method: external merge  Disk: 45576kB"
"              ->  Hash Join  (cost=16054.09..67166.85 rows=396200 width=100) (actual time=605.195..2788.949 rows=396200 loops=1)"
"                    Hash Cond: (lnk.pos_id = hub_pos.pos_id)"
"                    ->  Merge Join  (cost=3.16..35094.17 rows=396200 width=47) (actual time=0.025..1333.363 rows=396200 loops=1)"
"                          Merge Cond: (lnk.pos_record_id = hub_rec.pos_record_id)"
"                          ->  Index Scan using i_lnk_pos_record_pos_record_id on lnk_pos_record lnk  (cost=0.42..14026.08 rows=396200 width=16) (actual time=0.009..385.476 rows=396200 loops=1)"
"                          ->  Index Scan using i_hub_pos__record_pos_record_id on hub_pos_record hub_rec  (cost=0.42..15126.22 rows=396520 width=39) (actual time=0.009..548.958 rows=396520 loops=1)"
"                    ->  Hash  (cost=8245.19..8245.19 rows=335819 width=61) (actual time=604.659..604.659 rows=335819 loops=1)"
"                          Buckets: 65536  Batches: 16  Memory Usage: 2514kB"
"                          ->  Seq Scan on hub_pos  (cost=0.00..8245.19 rows=335819 width=61) (actual time=0.003..396.353 rows=335819 loops=1)"
"Planning time: 573.134 ms"
"Execution time: 13679.010 ms"