Get Values That Only Appear Once

I am trying to write a query that will spit out the subject_key’s that only appear a single time in the public.message table. What I have below is spitting out keys for people who have more than 1 message so I am missing something but am unsure what I am doing wrong.

with cte AS
(
  select subject_key,

       row_number() over(partition by subject_key 
                          ) as RN
   
   from public.message
 )
 select *
 from cte
 where rn = 1 LIMIT 10

>Solution :

With this query, every subject key will get its own numbering, and by definition, every one will have a row that’s the first (i.e., has rn=1). A simpler aggregate query should do the trick:

SELECT   subject_key
FROM     public.message
GROUP BY subject_key
HAVING   COUNT(*) = 1