Sql Query Get distinct rows based on a column-CodePudding

I have a table that contains a column named Ref and another column named status I need to get all the rows when status='On', then I should take all records when status=OFF and Ref are not taken from the beginning while taking the "On" status.

Here is an example

CodePudding user response：

Here is one way

drop table if exists #have;

create table #have
(
  Ref      [int]
, Status   [Varchar](8)
, Link     [Varchar](8)
)
;

insert into #have
values
  (66, 'OFF'    , 'A')
, (76, 'OFF'    , 'B')
, (12, 'OFF'    , 'C')
, (15, 'OFF'    , 'D')
, (12, 'ON'     , 'E')
, (15, 'ON'     , 'F')
, (25, 'ON'     , 'G')
, (27, 'ON'     , 'H')
, (25, 'Waiting', 'K')
;

select *
from #have
where Status = 'ON'
union
select * 
from #have
where status = 'OFF'
and REF not in (select REF from #have where Status = 'ON')
;

CodePudding user response：

Change status as sortable value like integer.

SELECT *
FROM example_table;

ref|link|status|
--- ---- ------ 
 12|E   |ON    |
 15|F   |ON    |
 25|G   |ON    |
 27|H   |ON    |
 66|A   |OFF   |
 76|B   |OFF   |

Map 'ON' to 1, 'OFF' to 0, 'Wainting' to -1

SELECT "ref", link, status 
FROM (
    SELECT "ref", link, status, 
        ROW_NUMBER() OVER (
            PARTITION BY "ref" 
            ORDER BY CASE status WHEN 'ON' THEN 1 WHEN 'OFF' THEN 0 WHEN 'Wainting' THEN -1 END DESC
            ) AS row_no
    FROM example_table
    ) v
WHERE v.row_no = 1

ref|link|status|
--- ---- ------ 
 12|E   |ON    |
 15|F   |ON    |
 25|G   |ON    |
 27|H   |ON    |
 66|A   |OFF   |
 76|B   |OFF   |

This query reads the table only once.
And also sorts only once.
Here is execution plan (in Postgresql)