Rather basic SELECT question

[Chikara]

Let's say have a Query like this.

SELECT * From Products ORDER By ProductId ASC

Let's say this returns 50 rows. If I wanted to only view rows 10 - 20 is there a SQL command that would do this?

Basically I'm going to divide a SQL Query into sets of 10 to view on ASP pages. Does SQL have the capability of doing this or should I look for an ASP solution?

[bubbisthedog]

What type of DB are you using (MySQL, SQL Server, etc.)? I see that you're using ASP, but I'd prefer not to assume that you're using SQL Server.

[Chikara]

Thanks for the response!

Yes, I just realized that my DB would be useful. I'm using SQL 2000. I was looking around and found the LIMIT reserved word. This is exactly what I need to do, but it seems that it only works in MySQL. Is there a similar SQL 2000 command?

[bubbisthedog]

Untested, but give this a try (assumes that you have a primary key in your table [which you always should!]):

select top x *
from tablename
where primary key
not in
(select top x + 10 primary key
from tablename
order by field A))
order by field A

[Chikara]

When I attempt to use that Query I recieve this error. Any ideas?

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'primary'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'primary'.

[bubbisthedog]

The italicized words in the query need to be replaced with actual field and table names.

E.g., to get the 'second' page of results:

select top 10 *
from PRODUCTS
where ProductId
not in
(select top 20 ProductId
from PRODUCTS
order by ProductId)
order by ProductId


E.g., to get the 'fifth' page of results:

select top 40 *
from PRODUCTS
where ProductId
not in
(select top 50 ProductId
from PRODUCTS
order by ProductId)
order by ProductId

[Chikara]

ha. I had everything correct, but was using Primary Key and thinking it was correct as those are reserved words. Thank you very much for the help.

[bubbisthedog]

You're quite welcome. Glad it worked.

[Chikara]

I was playing around with that Query trying to apply it to some more complicated SQL Queries and ran into a problem.

Select top 10 products.shortdescription, ProductProductCategoryXRef.ProductId
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
order by shortdescription ASC

Basically this returns the top 10 products located in a table based on a vendor id and a product category.

I want to apply the same kind of method listed above to return pages of data.

When I try to apply the same idea above I get this SQL query.

Select top 10 products.shortdescription, ProductProductCategoryXRef.ProductId
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
AND ProductProductCategoryXRef.ProductId not in(select top 10 *
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
order by shortdescription ASC
)
order by shortdescription ASC

However, when I try to run it I get this error.
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Any ideas?

[Chikara]

I figured out my problem, but if someone could double check my understanding of why this Query worked I would appreciate it!

WORKING QUERY
Select top 10 products.shortdescription, ProductProductCategoryXRef.ProductId
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
AND ProductProductCategoryXRef.ProductId not in(Select top 10 ProductProductCategoryXRef.ProductId
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
order by shortdescription ASC
)
order by shortdescription ASC

BROKEN QUERY

Select top 10 products.shortdescription, ProductProductCategoryXRef.ProductId
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
AND ProductProductCategoryXRef.ProductId not in(Select top 10 ProductProductCategoryXRef.ProductId Products.ShortDescription
From Products,productproductcategoryxref
where Products.VendorId = '1442'
AND Productproductcategoryxref.productcategoryid = '17'
AND productproductcategoryxref.productid = products.productid
order by shortdescription ASC
)
order by shortdescription ASC


This didn't work because I was attempting to compare productproductcategory.productid to a subquery that returned 2 things, productproductcategory.productid and products.shortdescription. Is that correct?

[bubbisthedog]

This isn't meant to be rude :) , but you need to understand the intent of the query.

a) Selecting the top 10 in both select statements will result in 0 records returned. If you're wanting to return 10 records at a time, the 'top' in the subquery must be 20.

b) The 'not in' before the subquery is testing to see if the productid exists in the result set of the subquery. If you were to literally write it out, it would look like this:

not in ('1', '2', '3', ...)

The point is that you can only have one field in the 'select' clause of your subquery (in this case, it would be productid).

c) If you have duplicate short descriptions, it is possible that some records will not be returned.

[Chikara]

Not rude at all. I'm attempting to teach myself some SQL so anything help. Strangely enough when I run that query with top 10 in both statements does in fact return the top 10 that I'm looking for. Could it be that my first select statement is logically flawed?

This is how my query is working now. I'm going to use the original code that you posted but change some variable names

select top x *
from tablename
where primary key
not in
(select top Y + 10 primary key
from tablename
order by field A))
order by field A
x would be the total size returned by the query.
Y would be the offset. In other words this will be the first row returned. A value of 10 would mean start at 10.

I do not have a strong background in SQL, so I'm not exactly why this would work. If this syntax is doing something it shouldn't, that would likely mean that the first select statement could be logically flawed right?

I know this looks complicated, but I really appreciate any help or comments.

[bubbisthedog]

I'm starting to think that using logic, not SQL, to get your expected results will be the best way to do this. It would be relatively easy actually. Are you experienced enough to do this? If not, it might take a little while, but I could try to walk you through it.

[Chikara]

Have pretty solid grasp of ASP, however I was hoping that I could use one or two SQL statements to save myself a lot of extra coding. It would be rather easy to use a while loop to just have ASP display the values that I want.

I was hoping to have this search feature run more effectively using a SQL call that just returns what I need.

Any thoughts?

[bubbisthedog]

The fact the setting x to 10 in both parts of the query yielded results is an indication that my suggestion did not work. Here's another suggestion that may work (untested):

select top x field list for tablename
from tablename
join
(select top x + 10 *
from tablename) ta
on ta.primary key <> tablename.primary key

[Chikara]

I'm finishing up for the day. I will try this on Monday and get back to you. Thanks for the help!