CODEWITHSUNDEEP
sql-serversql-server-2012subquery
DAT BOI
DAT BOI

Reputation: 183

SQL: how to list values of a column that are not the 5 most occurring value of that same column?

I understand how to display the 5 most occurring value of a column like so:

select top 5 col1, count(col1)
from table1
group by col1
order by count(col1) desc;

However, how do I create a query that displays all other values of the same column that are not in the result of the above query?

I tried the following sub query:

select col1
from table1
where col1 not in 
    (select top 5 col1, count(col1)
     from table1
     group by col1
     order by count(col1) desc);

However the query failed and I got the following error message:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 2

Views: 102

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

For Sql Server 2012+ you can use offset:

select col1, count(col1)
from table1
group by col1
order by count(col1) desc
offset 5 rows

You may want to add tiebreaker to your ordering here to make it deterministic:

select col1, count(col1)
from table1
group by col1
order by count(col1) desc, col1
offset 5 rows

Upvotes: 1

Eric
Eric

Reputation: 5743

Use OFFSET

select col1, count(col1)
from table1
group by col1
order by count(col1) desc
OFFSET 5 ROWS -- skip 5 rows, must use with order by

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Problem is you cannot select more than one column inside subquery. 

(select top 5 col1, count(col1)..

You can remove the count(col1) from subquery but NOT IN clause can fail when col1 in subquery has NULL values

Try changing like this

with cte as
(
select top 5 col1
from table1
group by col1
order by count(col1) desc
)
select * from table1 A
where not exists (select 1 from cte B where a.Col=b.col)

Upvotes: 0

Related Questions