p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   SELECT TOP n NOT SELECTING TOP n! (http://p2p.wrox.com/showthread.php?t=27492)

ibi March 30th, 2005 04:28 PM

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

Imar March 30th, 2005 04:33 PM

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?

ibi March 30th, 2005 04:36 PM

typo! rows..

sorry this is giving me a headache


ibi March 30th, 2005 04:42 PM

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


ibi March 30th, 2005 04:54 PM

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


Imar March 30th, 2005 05:12 PM

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?

rgerald March 30th, 2005 05:28 PM

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

rgerald March 30th, 2005 05:39 PM

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

Rand

ibi March 30th, 2005 08:08 PM

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,



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

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