SQL queries to get the duplicate records based on trim

group byoracleoracle-11g-r2querytrim

I have a table that has got the below set of records.

UNMASK  MASK
123 897609
00123   896509
0000456 5632789
456 32567889
5678    5632789

From the above table I need to select only the below records.

(I.E) Whatever values in UNMASK has any number of '0' before the '1-9' and also the row that doesn't have 0s before that 1-9.

Like 123 and there can be 000000123, 456 and 00456, I need such records(only Prefixed with 0s and the original value) in the output using SQL query in Oracle.

I tried the below query but it doesn't work the way I want.

Select UNMASK,TRIM (LEADING '0' FROM UNMASK) from HSA.TEST_TABLE group by UNMASK having count(TRIM(LEADING '0' FROM UNMASK)) > 1;

Best Answer

Test data:

create table test_table(unmask varchar(20), mask varchar(20));
insert into test_table values('123', '897609');
insert into test_table values('00123','896509');
insert into test_table values('0000456','5632789');
insert into test_table values('456','32567889');
insert into test_table values('5678','5632789');

I've done this with a CTE, as I love them. casting the string to a number removes the leading characters, which I find easier than messing around with TRIM.

Query:

with duplicates as 
(
  select cast(unmask as number) as unmask_num, count(*) as cnt
  from test_table
  group by cast(unmask as number)
  having count(*)>1
)
select *
from duplicates
join test_table on duplicates.unmask_num = cast(unmask as number)
;

DB Fiddle that demonstrates is here.

Related Solutions

Sql-server – Select query – group by and sub group by

If I have figured correctly, what you are after:

; WITH cte AS
  ( SELECT pid, uid, event, timeStamp,
           maxts = MAX(timeStamp) OVER (PARTITION BY pid, uid)
    FROM tableX
  )
SELECT pid, cnt = COUNT(*)
FROM cte
WHERE maxts = timeStamp
  AND event = 200 
GROUP BY pid ;

If there is a chance that you have 2 rows with identical pid, uid and timestamp, you'll need to replace COUNT(*) with COUNT(DISTINCT uid).

Postgresql Update Table row values by Grouped Column

Simple answer - as the dummy value in api_id doesn't matter: use the call_id:

update the_table 
    set api_id = call_id::text 
where api_id is null;

Complicated answer:

Assuming you have some way of generating a random string, you could do something like this (untested):

update the_table
   set api_id = t.random_string
from (
  select distinct on (call_id) call_id, random_string() as random_string
  from the_table
  where api_id is null
  order by call_id
) t 
where t.call_id = the_table.call_id
 and the_table.api_id is null;

This generates one row with per call_id in the inner query and generates a random value for each of that. This result is then used to update the target table.

The above assumes that if there is already value for a specific call_id there is one for all rows. It won't "fill" in missing api_id for other rows (e.g. if there was a row with call_id = 123 but no api_id)

Best Answer

Related Solutions

Sql-server – Select query – group by and sub group by

Postgresql Update Table row values by Grouped Column

Related Question