Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 March 30th, 2005, 04:28 PM
ibi ibi is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT TOP n NOT SELECTING TOP n!

Hey guys,

I've got a problem that's been giving me a headache and I can't get my head around it, hope you guys can inspire me!

I'm trying to execute a normal SELECT TOP 10 * FROM Table WHERE blah blah.. but it's selecting ALL fields and not the TOP 10!

This is my query:

SELECT TOP 10 * FROM Product WHERE ((Product.ManufacturerFeaturedOrder) Is Not Null) AND ((Product.ManufacturerID)=0) AND ((Product.Active)=True) ORDER BY Product.ManufacturerFeaturedOrder;

Please advise!

ibi
Reply With Quote
  #2 (permalink)  
Old March 30th, 2005, 04:33 PM
Imar's Avatar
Wrox Author
Points: 72,045, Level: 100
Points: 72,045, Level: 100 Points: 72,045, Level: 100 Points: 72,045, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,083
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Quote:
quote:but it's selecting ALL fields and not the TOP 10
Fields or rows? Is this a typo, or do you expect TOP to limit the number of rows?

What database are you using?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Have a little faith in me(bonus live) by Ilse De Lange (Track 14 from the album: Here I Am - 1998-2003) What's This?
Reply With Quote
  #3 (permalink)  
Old March 30th, 2005, 04:36 PM
ibi ibi is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

typo! rows..

sorry this is giving me a headache

Reply With Quote
  #4 (permalink)  
Old March 30th, 2005, 04:42 PM
ibi ibi is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just realised how useless the info. I've given is.

I'm using MS Access through ADO using ASP.

There is another SQL query executed just before this one involving joins:

strSQL = "SELECT Category.CategoryID, Count(Category.Category) AS CountOfCategory, Category.Category, Category.Picture, Category.Comments, Product.Active, Product.AvailabilityID " &_
    "FROM Manufacturer INNER JOIN (Category INNER JOIN Product ON Category.CategoryID = Product.CategoryID) ON Manufacturer.ManufacturerID = Product.ManufacturerID " &_
    "GROUP BY Category.CategoryID, Category.Category, Category.Picture, Category.Comments, Manufacturer.ManufacturerID, Category.Display, Product.Active, Product.AvailabilityID " &_
    "HAVING (((Manufacturer.ManufacturerID)=" & man & ") AND ((Category.Display)=True) AND ((Product.Active)=True) AND ((Product.AvailabilityID)=0)) " &_
    "ORDER BY Category.Category;"

SET rsCategories = Server.CreateObject("ADODB.Recordset")
rsCategories.Open strSQL, Con, adOpenStatic, adLockReadOnly, adCmdText

The above is working fine. I thought perhaps the constants of the recordset object could possibly interfere with the next SELECT TOP statement actually not selecting the TOP rows but ALL rows?

Thnx

Reply With Quote
  #5 (permalink)  
Old March 30th, 2005, 04:54 PM
ibi ibi is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is what I've got now:

SELECT TOP 10 * FROM Product WHERE ((ManufacturerFeaturedOrder) Is Not Null) AND ((ManufacturerID)=" & man & ") AND ((Active)=True) ORDER BY ManufacturerFeaturedOrder;

It works if I use:

SELECT TOP 10 * FROM Product;

i.e without any WHERE clause.

So the problem is not with the database or connection but the query itself??

ibi

Reply With Quote
  #6 (permalink)  
Old March 30th, 2005, 05:12 PM
Imar's Avatar
Wrox Author
Points: 72,045, Level: 100
Points: 72,045, Level: 100 Points: 72,045, Level: 100 Points: 72,045, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,083
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

What happens when you drop all the ( and ) in the WHERE statement?

Then use Response.Write to write out the SQL statement and run it in the query analyzer of Access. What do you get?

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Brand New You're Retro by Tricky (Track 8 from the album: Maxinquaye) What's This?
Reply With Quote
  #7 (permalink)  
Old March 30th, 2005, 05:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try using brackets and not parentheses around the field names like this:

Code:
SELECT TOP 10 * FROM Product  WHERE ([ManufacturerFeaturedOrder] Is Not Null) AND ([ManufacturerID]=" & man & ") AND ([Active]=True) ORDER BY ManufacturerFeaturedOrder;
Rand
Reply With Quote
  #8 (permalink)  
Old March 30th, 2005, 05:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Since none of the field name contain spaces or special characters, you may be able to eliminate all of the parentheses and brackets.

Rand
Reply With Quote
  #9 (permalink)  
Old March 30th, 2005, 08:08 PM
ibi ibi is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

guys,

Removing the paranthesis didn't change anything. Removing the WHERE clause altogether DID extract only the top 10 rows.

I tried this.... and it works....

strSQL = "SELECT TOP 10 * FROM (SELECT Product.* " &_
    "FROM Product " &_
    "WHERE (((Product.ManufacturerFeaturedOrder) Is Not Null) AND ((Product.ManufacturerID)=" & man & ") AND ((Product.Active)=True)) " &_
    "ORDER BY Product.ManufacturerFeaturedOrder)"

So I'm selecting the TOP 10 from the query which I was originally trying to take out the top 10 from!

Now this should work in theory.. which it does.... but it's unnecessary.. It should work straight out.

I removed the check for NULL fields and also the WHERE clause altogether, keeping just the ORDER BY clause, still having the total number of rows returned!

So it isnt the WHERE clause either.. I've got it working now.. just confused over why it's not working how it *should*

Hope this post might help someone in the future, and any ideas on the problem still appreciated!

Cheers,

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
Selecting top record of a group by clause lic023 Access 7 June 7th, 2006 11:25 AM
SELECT TOP n rgerald SQL Server 2000 3 May 12th, 2006 04:03 PM
SELECT TOP FROM HAVING khatfield29 SQL Language 1 August 23rd, 2004 02:41 PM
SELECT TOP !!!! Jane SQL Language 2 October 17th, 2003 11:22 AM
SELECT TOP ciko1973 SQL Language 5 August 8th, 2003 04:35 AM



All times are GMT -4. The time now is 04:15 PM.


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