Search code examples
t-sqlgroup-byhaving

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

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.

Solution

  • 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