Top 10 on a Count 1

[dvannoy]

I am trying to get the top 10 results of the count (field) as total..

below is a sample of what iam doing


select count(field) as Total, field1,field2
from table
where field1 <>'co'
and (not(datefield is null))
group by field1,field2
order by field1

any help would be appreciated
thanks

 

[dmcmunn]

select top 10 * from
(
select count(field) as Total, field1,field2
from table
where field1 <>'co'
and (not(datefield is null))
group by field1,field2
order by field1
)

 

[dvannoy]

error message says incorrect syntax near ')'

thats what I was getting before. I know how to get the top 10 records , but the count field seems to be different

thanks

 

[jms8222]

Does this get you what you're looking for:

select TOP 10 field1,field2
from table
where field1 <>'co'
and (not(datefield is null))
group by field1,field2
order by field1
compute count(field1)

 

[SQLBill]

Try this:

[b]SET ROWCOUNT 10[/b]
select count(field) as Total, field1,field2
from table
where field1 <>'co'
and (not(datefield is null))
group by field1,field2
order by [b]TOTAL ASC[/b]

You may have to change the ORDER BY to DESC depending on what you want. ASC will give you counts from minimum to maximum, DESC will be opposite.

SET ROWCOUNT works similar to TOP. But whichever you use, you have to ORDER BY the proper field. You were ORDERing BY the field1 and not by the COUNT.

-SQLBill

 

[SQLSister]

The reason why dmcmunn's query did not work (aside from the order by issue that SQL Bill pointed out) is that it used a derived table. When you do that, you need to alias the table.

 

[dvannoy]

I am sorry guys, i should have given more detail..I need to get the TOP 10 highest numbers from the count not just the top 10 records. sorry for the mix up.

Thats why I was asking for a top 10 on the count. I need to get the top 10 highest results from the count

Thanks

 

[SQLBill]

Do you mean you need duplicate counts?
ie. TOP 3:
4
3
3


OR TOP 3 WITH TIES
4
3
3
2

If you want the duplicates:

[code}
select TOP 10 WITH TIES count(field) as Total, field1,field2
from table
where field1 <>'co'
and (not(datefield is null))
group by field1,field2
order by TOTAL DESC
[/code]

-SQLBill

 

[dvannoy]

SQLBill ,Thank you...

 

[SQLBill]

You're welcome and thanks for the star.

-SQLBill