How to select the value that exist in every single row sql-CodePudding

I need to check if a value of a column "nS" is the same for every single row of the table "List", then display that value.

Example01: This should return 'S1' because every row has that value in the column 'nS'.

|  nS  |  nE  |
|:-----|-----:|
| 'S1' | 'A1' |
| 'S1' | 'A2' |
| 'S1' | 'A3' |
| 'S1' | 'A4' |

Example02: This should not return anything because the values of 'nS' are different.

|  nS  |  nE  |
|:-----|-----:|
| 'S1' | 'A1' |
| 'S2' | 'A2' |
| 'S1' | 'A3' |
| 'S3' | 'A4' |

So far i did the code below but it doesn't work because the GROUP BY statement.

SELECT nS FROM List GROUP BY nS HAVING count(nS) = count(*);

CodePudding user response：

Something like this?

SQL> with test (ns, ne) as
  2    (select 's1', 'a1' from dual union all
  3     select 's1', 'a2' from dual union all
  4     select 's1', 'a3' from dual union all
  5     select 's1', 'a4' from dual
  6    )
  7  select distinct ns
  8  from test
  9  where 1 = (select count(distinct ns) From test);

NS
--
s1

SQL>
SQL> with test (ns, ne) as
  2    (select 's1', 'a1' from dual union all
  3     select 's2', 'a2' from dual union all
  4     select 's1', 'a3' from dual union all
  5     select 's3', 'a4' from dual
  6    )
  7  select ns
  8  from test
  9  where 1 = (select count(distinct ns) From test);

no rows selected

SQL>

CodePudding user response：

In your query you must replace COUNT(*) with a subquery that returns the total number of rows in the table:

SELECT nS
FROM List
GROUP BY nS
HAVING COUNT(*) = (SELECT COUNT(*) FROM List);