0
select id,name,major,mark from table where id='001'

id  name  major   mark
001 peter english 90
001 peter art     85


---What I want is: ----

(if the major distinct count >1, show the data without english)

id  name  major   mark
001 peter art     85


(if the major distinct count =1, just displayed as usual)

id  name  major   mark
002 annie english   77

or

id  name  major   mark
003 ken   math     82

I don't want to show English if the major distinct count >1, otherwise just displayed as usual. Any idea on how I do this?

Any help would be appreciated.

rorona
  • 25
  • 5
  • How do you identify which row to remove: English or Art? – astentx May 10 '23 at 05:56
  • 1
    Add some more sample data, and also _specify_ the expected result. I.e. a [mcve]. – jarlh May 10 '23 at 06:27
  • Does this answer your question? [How to get only one record for each duplicate rows of the id in oracle?](https://stackoverflow.com/questions/19920243/how-to-get-only-one-record-for-each-duplicate-rows-of-the-id-in-oracle) – astentx May 10 '23 at 06:58
  • 3 alternative results? – jarlh May 10 '23 at 08:23

2 Answers2

1

I don't want to show English if the major distinct count >1, otherwise just displayed as usual.

select id, name, major, mark
from (select id, name, major, mark, 
             count(distinct major) over (partition by id) cnt 
      from table_name)
where cnt = 1 or major <> 'English'

dbfiddle demo

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Thank for help! Finally I fixed a bit and it worked. where((SELECT COUNT(DISTINCT major) FROM table where id = '001') > 1 AND major <> 'english' and id = '001')OR ((SELECT COUNT(DISTINCT major) FROM table where id = '001') <= 1AND id = '001' )) – rorona May 11 '23 at 03:47
0

Here's one option. Read comments within code.

Sample data:

SQL> with
  2  test (id, name, major, mark) as
  3    (select 1, 'Peter', 'English', 90 from dual union all  --> Peter has 2 majors, so - omit English
  4     select 1, 'Peter', 'Art'    , 85 from dual union all
  5     select 2, 'John' , 'Maths'  , 20 from dual union all  --> Display Maths for John
  6     select 3, 'Mike' , 'English', 50 from dual            --> Display English for Mike
  7    ),

Query begins here:

  8  temp as
  9    -- Count number of distinct major values per each ID
 10    (select id, count(distinct major) cnt_dm
 11     from test
 12     group by id
 13    )
 14  -- Join the "original" table with TEMP so that you could check whether number of major values
 15  -- is greater than 1; if so, don't display English. Otherwise, display what you have.
 16  -- This presumes that there's no major whose name is 'x'
 17  select a.*
 18  from test a join temp b on a.id = b.id
 19  where major <> case when b.cnt_dm > 1 then 'English'
 20                      else 'x'
 21                 end;

        ID NAME  MAJOR         MARK
---------- ----- ------- ----------
         1 Peter Art             85
         2 John  Maths           20
         3 Mike  English         50

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57