2

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.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
DAT BOI
  • 183
  • 1
  • 1
  • 10

3 Answers3

1

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
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
0

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)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

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
Eric
  • 5,675
  • 16
  • 24