Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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......
  #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]

  #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......
  #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
================================================== =========
  #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()

  #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......
  #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


  #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......
  #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)
  #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




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





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