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
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')
;
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)
