Selecting row(s) that have distinct count (one) of certain column-CodePudding

I have following dataset:

org   system_id     punch_start_tb1         punch_start_tb2
CG    100242        2022-08-16T00:08:00Z    2022-08-16T03:08:00Z
LA    250595        2022-08-16T00:00:00Z    2022-08-16T03:00:00Z
LB    300133        2022-08-15T04:00:00Z    2022-08-16T04:00:00Z
LB    300133        2022-08-16T04:00:00Z    2022-08-15T04:00:00Z
MO    400037        2022-08-15T14:00:00Z    2022-08-15T23:00:00Z
MO    400037        2022-08-15T23:00:00Z    2022-08-15T14:00:00Z

I am trying to filter out data so that it only populates the outcome when Count of "system_id" = 1.

So, the expected outcome would be only following two rows:

org   system_id     punch_start_tb1         punch_start_tb2
CG    100242        2022-08-16T00:08:00Z    2022-08-16T03:08:00Z
LA    250595        2022-08-16T00:00:00Z    2022-08-16T03:00:00Z

I tried with Group by and Having clause, but I did not have a success.

CodePudding user response：

You can try below

SELECT * FROM
 (
    SELECT org,system_id,punch_start_tbl,punch_start_tb2
    ,ROW_NUMBER()OVER(PARTITION BY system_id ORDER BY system_id)RN
    FROM <TableName>
)X 
WHERE RN = 1

CodePudding user response：

CTE returns org with only one record then join with main table on org column.

;WITH CTE AS (
select org
    from <table_name>
    group by org
    Having count(1) = 1
)
select t.*
    from cte 
        inner join <table_name> t on cte.org = t.org

CodePudding user response：

You can try this (use min because we have only one row):

select MIN(org), system_id, MIN(punch_start_tb1), MIN(punch_start_tb2)
from <table_name>
group by system_id     
Having count(1) = 1

or use answer @Meyssam Toluie with group by by system_id