Paging query

[Petr Felzmann]

Hello,

I have simple table MyTable with 2 columns:
1. name: code, type: text, primary key
2. name: title, type: memo

I need obtain the second ten (11. - 20.) rows defined by
LIKE and ORDER.

This select is OK:

SELECT TOP 10 *
FROM [SELECT TOP 10 *
FROM MyTable WHERE code IN
(SELECT TOP 20 code FROM MyTable
WHERE code LIKE '22%' ORDER BY code)
ORDER BY code DESC]. AS tmp
ORDER BY tmp.code;

But this select, almost the same, only one different is
that LIKE is performed on column title instead of code:

SELECT TOP 10 *
FROM [SELECT TOP 10 *
FROM MyTable WHERE title IN
(SELECT TOP 20 title FROM MyTable
WHERE title LIKE '%concrete%' ORDER BY code)
ORDER BY code DESC]. AS tmp
ORDER BY tmp.code;

I obtain error:

Invalid object Memo, OLE or Hyperlink in subquery title.

Any sugestion, please? Petr

P.S. In MS SQL 2000 server is evreything OK.

 

[Brian Camire]

You might try another approach using a query whose SQL looks something like
this:

SELECT
MyTable.*
FROM
MyTable
WHERE
(SELECT
COUNT(*)
FROM
MyTable AS Self
WHERE
Self.title LIKE '%concrete%'
AND
Self.code <= MyTable.code) BETWEEN 11 AND 20
AND
MyTable.title LIKE '%concrete%'
ORDER BY
MyTable.code

Depending on how you execute the query you may need to use the * wildcard
charcter instead of %.