SQL query to return the 10 largest numbers

say, I have a detailed list of telephone calls and wish to extract the top 10 longest calls and the top 10 most frequently dialed numbers. What should the query/s look like? I'm using MS ACCESS.
shaunwinginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zicevaCommented:
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)
zicevaCommented:

SELECT * FROM `table_name` ORDER BY `call_length` DESC LIMIT 10;
MINDSUPERBCommented:
Top 10 in an Ordered Query
SELECT TOP 10 [column_1], [column_2]
FROM [table_name]
ORDER BY [column_1];
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

shaunwinginAuthor Commented:
Tx but in MS Access there is not LIMIT option


Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will remove the MySQL zone, as you posted that you are using MS Access.the 2 first suggestions won't work henceto get, in ms access, the longest calls would read like this:
select top 10 * from yourtable order by call_duration_field desc

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the second thing
SELECT TOP 10 CALLED_NUMBER
  FROM YOURTAble
 group by called_number
order by count(*) desc

Open in new window

MINDSUPERBCommented:
shaunwingin,

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

Sincerely,
Ed
Query-Top-Records.jpg
shaunwinginAuthor Commented:
It has the TOP command.
How do I get the top 10  frequently dialed no?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see my comment ...
question: do you want both in 1 query?
shaunwinginAuthor Commented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first, you need that query to work, and eventually save that as query (view) or table ...
then, you query your current table using that results:


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

Open in new window

shaunwinginAuthor Commented:
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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)

Open in new window

shaunwinginAuthor Commented:
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)));
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)

Open in new window

shaunwinginAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

shaunwinginAuthor Commented:
Tx very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.