get first ten records

MunishBhatia · December 4th, 2007, 01:05 PM

how to get top ten rows from a table.
please guide me about query.

thanks......

Smeghead · December 4th, 2007, 03:57 PM

Try this

SELECT TOP 10 * FROM [TABLENAME]

MunishBhatia · December 4th, 2007, 11:21 PM

Great thanks
i want to modify this query to get 10 records from table who get max(calls).
Please also help me in this.

thanks......

dparsons · December 4th, 2007, 11:51 PM

O.o This is rudimentary and general searches on google should have netted you the answer that you want. Did you try to find the answer yourself?

Also, max() is an aggregate and is only ever going to return you 1 value: the max value in the column calls so it can't really be used with top.

Explain what you are trying to do more accurately.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

Smeghead · December 6th, 2007, 09:48 AM

Are you trying to find out if you can get the amount of times a certain record is called from a stored procedure or are you trying to find out if you can find the maximum times the record has been duplicated and get a count on that by using the HAVING count (*)> 1
clause. Or are you trying do as the previous post suggests using MAX()

MunishBhatia · December 7th, 2007, 12:26 AM

i am trying to get only ten records from table where :
calls used is one of my column in my table.i want 10 records who used highest call like 15 14 etc
so 10 records having highest values in calls used inb descending order.

thanks......

Peso · December 7th, 2007, 04:13 AM

1)
SELECT * FROM Table1 ORDER BY Calls DESC

2)
SELECT AgentID, SUM(Calls)
FROM Table1
GROUP BY AgentID
ORDER BY SUM(Calls) DESC

MunishBhatia · December 7th, 2007, 11:01 AM

well yours second qury is a good logical example but i feel i am not able to write my problem precisely. i am explaining it with example:
i have following table.

Agent ID Calls used
1 10
2 8
3 70
4 9
5 12
6 5
7 21
8 33
9 44
10 52
11 12
12 65
13 7
14 89
15 45
16 45
17 35
18 36
19 85
20 22

now if i arrange calls used in descending order i will get highest number first, then second highest and so on. from these list of all highest i need only first 10.

so i need 10 records where calls used are first to 10th highest in descending order.
now i want only ten records from this table.

thanks......

Shasur · December 9th, 2007, 07:26 AM

I would also suggest you to use 'With Ties' option in case your last (tenth call is tied

http://sqldud.blogspot.com/2007/04/t...with-ties.html

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Jeff Moden · December 9th, 2007, 10:21 AM

Quote:

quote:Originally posted by MunishBhatia
Great thanks
i want to modify this query to get 10 records from table who get max(calls).
Please also help me in this.

thanks......

Unless I just don't understand what you're asking for, this is pretty easy to accomplish...

Code:

--===== Create a demonstrable test table
     -- This is NOT part of the solution
DECLARE @TestData TABLE ([Agent ID] INT, [Calls Used] INT)
 INSERT INTO @TestData ([Agent ID],[Calls Used])
 SELECT 1,10 UNION ALL
 SELECT 2,8 UNION ALL
 SELECT 3,70 UNION ALL
 SELECT 4,9 UNION ALL
 SELECT 5,12 UNION ALL
 SELECT 6,5 UNION ALL
 SELECT 7,21 UNION ALL
 SELECT 8,33 UNION ALL
 SELECT 9,44 UNION ALL
 SELECT 10,52 UNION ALL
 SELECT 11,12 UNION ALL
 SELECT 12,65 UNION ALL
 SELECT 13,7 UNION ALL
 SELECT 14,89 UNION ALL
 SELECT 15,45 UNION ALL
 SELECT 16,45 UNION ALL
 SELECT 17,35 UNION ALL
 SELECT 18,36 UNION ALL
 SELECT 19,85 UNION ALL
 SELECT 20,22

--===== The solution
 SELECT TOP 10 * 
   FROM @TestData
  ORDER BY [Calls Used] DESC

--Jeff Moden