Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 4th, 2007, 01:05 PM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default get first ten records

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

thanks......
__________________
thanks......
Reply With Quote
  #2 (permalink)  
Old December 4th, 2007, 03:57 PM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this

SELECT TOP 10 * FROM [TABLENAME]

Reply With Quote
  #3 (permalink)  
Old December 4th, 2007, 11:21 PM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default

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......
Reply With Quote
  #4 (permalink)  
Old December 4th, 2007, 11:51 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #5 (permalink)  
Old December 6th, 2007, 09:48 AM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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()

Reply With Quote
  #6 (permalink)  
Old December 7th, 2007, 12:26 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default

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......
Reply With Quote
  #7 (permalink)  
Old December 7th, 2007, 04:13 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

1)
SELECT * FROM Table1 ORDER BY Calls DESC

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


Reply With Quote
  #8 (permalink)  
Old December 7th, 2007, 11:01 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default

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......
Reply With Quote
  #9 (permalink)  
Old December 9th, 2007, 07:26 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)
Reply With Quote
  #10 (permalink)  
Old December 9th, 2007, 10:21 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Chapter ten JPSpan Creed666 BOOK: Professional Ajax ISBN: 978-0-471-77778-6 8 April 18th, 2014 01:37 AM
How to maintain history of last ten visited page i vishnu108mishra ASP.NET 2.0 Professional 1 February 29th, 2008 01:27 PM
problem in updating records & finding records naveed77 VB Databases Basics 1 January 16th, 2007 12:12 PM
problem in updating records & finding records naveed77 VB How-To 1 January 16th, 2007 12:10 PM
how many records keyvanjan ASP.NET 1.0 and 1.1 Basics 2 September 7th, 2006 02:52 AM



All times are GMT -4. The time now is 03:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.