Conditional selecting rows based on a column value
19,304
Solution 1
try like this...
Select * from tablename where flag=1
union
(Select * from tablename a where (Select count(*) from tablename b
where a.Range_id=b.RANGE_ID and b.flag=1)<1)
Solution 2
Try this.
select * from myTable
where flag_line = 1
or
(range_id, start_date, end_date, band_type) in (
select range_id, start_date, end_date, band_type
from myTable
group by range_id, start_date, end_date, band_type
having max(flag_line) = 0)
Author by
03Usr
Updated on June 15, 2022Comments
-
03Usr almost 2 years
ID RANGE_ID START_DATE END_DATE BAND_TYPE FLAG_LINE 3 1 01/03/2013 31/03/2013 R 1 4 1 01/03/2013 31/03/2013 R 0 5 2 01/03/2013 31/03/2013 R 1 6 2 01/03/2013 31/03/2013 R 0 7 3 01/03/2013 31/03/2013 R 0 8 3 01/03/2013 31/03/2013 N 0
From this table, for each RANGE_ID, I need to select rows using the following conditions: If there are rows with identical columns (apart from the ID field) then only select the row which has FLAG_LINE = 1, if there are identical rows but none of them contain a FLAG_LINE=1 column then select all of them, based on this the query should return the following results:
ID RANGE_ID START_DATE END_DATE BAND_TYPE FLAG_LINE 3 1 01/03/2013 31/03/2013 R 1 5 2 01/03/2013 31/03/2013 R 1 7 3 01/03/2013 31/03/2013 R 0 8 3 01/03/2013 31/03/2013 N 0
I tried doing it in chunks: i.e run something similar for each RANGE:
begin for x in ( select count(*) cnt from dual where exists ( select 1 FROM myTable WHERE RANGE_ID = 1 AND FLAG_LINE = 1) ) loop if ( x.cnt = 1 ) then dbms_output.put_line('flag line exists'); --insert the line with FLAG_LINE = 1 into temp table for this range else dbms_output.put_line('does not exist'); --insert the lines into temp table for this range end if; end loop; end;
using this method for each RANGE I populate a temp table and return the results at the end, but this is not quite flexible, is there another way that this can be achieved?
Thanks