SQL query to return the 10 largest numbers

It might look like this:

SELECT * FROM `table_name` ORDER BY `call_length` LIMIT 10;

(you must replace `table_name` and `call_length` with the actual names for the table and field name respectively)

SELECT * FROM `table_name` ORDER BY `call_length` DESC LIMIT 10;

Top 10 in an Ordered Query
SELECT TOP 10 [column_1], [column_2]
FROM [table_name]
ORDER BY [column_1];

Tx but in MS Access there is not LIMIT option

for the second thing

SELECT TOP 10 CALLED_NUMBER
  FROM YOURTAble
 group by called_number
order by count(*) desc

Select allOpen in new window

shaunwingin,

You may see the attached image to get what you want.

Sincerely,
Ed
Query-Top-Records.jpg

It has the TOP command.
How do I get the top 10  frequently dialed no?

please see my comment ...
question: do you want both in 1 query?

Tx angelIII
The only catch is that I need all the fields returned not just the CalledNoField and don't believe the "group by" does.

angelIII,

How can I return the number of times each of the top 10 frequently dialed no's was called?
I.e. a count of CalledNoField in the query above please?

again, a couple of ways to shave the cat...

select top 10 CalledNoField, count(*) from yourtable group by CallednoField order by count(*) desc


aka:

select t.*
, ( select top 10 count(*) from yourtable group by CallednoField order by count(*) desc) cnt
from  yourtable t 
where CalledNoField in ( select top 10 CalledNoField from yourtable group by CallednoField order by count(*) desc)

Select allOpen in new window

Hi, Tx.
This is what I now have, but get error that at most one record can be returned by this subquery.

SELECT qtTelkomDETAILRawByInvNo.*, (select top 10 count(*) from qtTelkomDETAILRawByInvNo group by  [Final number called] order by count(*) desc)
FROM qtTelkomDETAILRawByInvNo
WHERE (((qtTelkomDETAILRawByInvNo.[Final number called]) In (SELECT TOP 10 [Final number called]
FROM qtTelkomDETAILRawByInvNo
GROUP BY [Final number called]
ORDER BY count(*) DESC)));

sorry, my fault:

select t.*
, ( select top 10 count(*) from yourtable x where x.CalledNoField = t.CalledNoField ) cnt
from  yourtable t 
where CalledNoField in ( select top 10 CalledNoField from yourtable group by CallednoField order by count(*) desc)

Select allOpen in new window

Tx, but Its just returning all the records by the looks of it, this is what I have:

SELECT T.*, (select top 10 count(*) from qtTelkomDETAILRawByInvNo TT WHERE TT.[Final number called] = T.[Final number called]) AS Expr1
FROM qtTelkomDETAILRawByInvNo AS T
WHERE (((T.[Final number called]) In (SELECT TOP 10 [Final number called]
FROM qtTelkomDETAILRawByInvNo
GROUP BY [Final number called]
ORDER BY count(*) DESC)));

Tx

yes, all the records for the top 10 most called final numbers.

if you only want the numbers and the counts

SELECT TOP 10 [Final number called],  count(*)
FROM qtTelkomDETAILRawByInvNo
GROUP BY [Final number called]
ORDER BY count(*) DESC

Select allOpen in new window

Tx very much.