Select TOP n * From table1

  • Thread starter Thread starter Saber
  • Start date Start date
S

Saber

I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
 
There is no such syntax for SQL. You'll have to use a WHERE clause that
excludes your first 10 records. How do you do that? Access doesn't have
"record numbers" as such, but you can create a query which numbers them. On
my website (see sig below) is a small sample database called
"NumberedQuery.mdb" which shows two ways to do it. It adds a column called
Sequence that numbers the query. You can add a TOP 10 predicate and also a
WHERE clause Where Sequence > 10.
 
Dear Saber:

You could do it like this:

SELECT TOP 20 * FROM tblPost ORDER BY Something

Now you want the "bottom 10" from this:

SELECT TOP 10 *
FROM (SELECT TOP 20 * FROM tblPost ORDER BY Something) X
ORDER BY Something DESC

You may then need to reverse the sorting this gives.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,


You can rank your records and make a criteria based on that:


SELECT LAST(a.f1), LAST(a.f2), LAST(a.f3), ... , LAST(fn)
FROM mTable As a INNER JOIN mTable As b
ON a.pk <= b.pk
GROUP BY a.pk
HAVING COUNT(*) BETWEEN this AND that


should do, where this and that are parameters. The <= produces an ASC rank
based on the value of the primary key field. Use >= to reverse the
ordering.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel,
It looks it is very complicated!
I write again here my exact current command:
strSql = "SELECT top 10 * from tblPosts,tblAuthors WHERE (tblPosts.authorid
= tblAuthors.id) ORDER BY tblPosts.id DESC"

I'm newbie and figured how to match my command with yours, I didn't got what
are 'a','f1,f2,..,fn','pk' in first lines and what is 'this' AND 'that' in
"HAVING COUNT(*) BETWEEN this AND that"

Sorry for that!

BTW- Perhaps the screen shot helps to see relations and field names:
http://maghalat.com/access.jpg


Michel Walsh said:
Hi,


You can rank your records and make a criteria based on that:


SELECT LAST(a.f1), LAST(a.f2), LAST(a.f3), ... , LAST(fn)
FROM mTable As a INNER JOIN mTable As b
ON a.pk <= b.pk
GROUP BY a.pk
HAVING COUNT(*) BETWEEN this AND that


should do, where this and that are parameters. The <= produces an ASC
rank
based on the value of the primary key field. Use >= to reverse the
ordering.



Hoping it may help,
Vanderghast, Access MVP


Saber said:
I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
 
Nice tip,
Thanks.
But what about next 10 records?
I mean (1-10, 11-20, 21-30, 31-40, ...)
I'm using ASP.Net, and exactly it is what I use to show first 10 posts:

strSql = "SELECT top 10 * from tblPosts,tblAuthors WHERE (tblPosts.authorid=
tblAuthors.id) ORDER BY tblPosts.id DESC"
BTW- Perhaps the screen shot helps to see relations and field names:
http://maghalat.com/access.jpg

Tom Ellison said:
Dear Saber:

You could do it like this:

SELECT TOP 20 * FROM tblPost ORDER BY Something

Now you want the "bottom 10" from this:

SELECT TOP 10 *
FROM (SELECT TOP 20 * FROM tblPost ORDER BY Something) X
ORDER BY Something DESC

You may then need to reverse the sorting this gives.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
 
I'm using an 'id' field as AutoNumber.
I can use the 'id' to show next 10 records, but what if
I delete a record in middle of table?
BTW- good examples in your website, thank you.


Roger Carlson said:
There is no such syntax for SQL. You'll have to use a WHERE clause that
excludes your first 10 records. How do you do that? Access doesn't have
"record numbers" as such, but you can create a query which numbers them.
On
my website (see sig below) is a small sample database called
"NumberedQuery.mdb" which shows two ways to do it. It adds a column
called
Sequence that numbers the query. You can add a TOP 10 predicate and also
a
WHERE clause Where Sequence > 10.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Saber said:
I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
 
Hi,


A total query cannot use * as in SELECT *, it has to enumerate all the
required field, explicitly. So, replace f1, f2, f3,... by the real field
name you want to select... because * cannot be use, here.

pk is to be replaced by the field used as primary key, you should have a
primary key for every table... you have, no?

If you want the first 10 records, use 0 for 'this' and that=10; if you
want the ten next records, use 11 for 'this', and use 20 for the parameter
'that' , and so on. They are parameters you specify before running the query
(as any other query using parameter).

Note that the 10 in SELECT TOP 10 ... cannot be a parameter. That is,
in part, why we have to compute the "rank" (first, second, third, .... ) of
the records.




Hoping it may help,
Vanderghast, Access MVP




Saber said:
Thanks Michel,
It looks it is very complicated!
I write again here my exact current command:
strSql = "SELECT top 10 * from tblPosts,tblAuthors WHERE (tblPosts.authorid
= tblAuthors.id) ORDER BY tblPosts.id DESC"

I'm newbie and figured how to match my command with yours, I didn't got what
are 'a','f1,f2,..,fn','pk' in first lines and what is 'this' AND 'that' in
"HAVING COUNT(*) BETWEEN this AND that"

Sorry for that!

BTW- Perhaps the screen shot helps to see relations and field names:
http://maghalat.com/access.jpg


Michel Walsh said:
Hi,


You can rank your records and make a criteria based on that:


SELECT LAST(a.f1), LAST(a.f2), LAST(a.f3), ... , LAST(fn)
FROM mTable As a INNER JOIN mTable As b
ON a.pk <= b.pk
GROUP BY a.pk
HAVING COUNT(*) BETWEEN this AND that


should do, where this and that are parameters. The <= produces an ASC
rank
based on the value of the primary key field. Use >= to reverse the
ordering.



Hoping it may help,
Vanderghast, Access MVP


Saber said:
I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
Click to expand...
 
Thanks for info,
I think it helps, but I get a syntax error:
Syntax Error (missing operator) in query expression '(tblPosts.authorid =
tblAuthors.id) As a INNER JOIN tblPosts As b ON a.id <= b.id'

here is what I use to show first 5 posts:
****************
SELECT LAST(tblPosts.id), LAST(tblPosts.posttitle), LAST(tblPosts.posttype),
LAST(tblPosts.postdate),LAST(tblPosts.posttopic),LAST(tblPosts.authorid),LAST(tblAuthors.id),LAST(tblAuthors.authorname),LAST(tblAuthors.authoremail),LAST(tblAuthors.authorurl),LAST(tblAuthors.authorphoto),LAST(tblAuthors.yim),LAST(tblAuthors.icq)
FROM tblPosts,tblAuthors WHERE (tblPosts.authorid = tblAuthors.id) As a
INNER JOIN tblPosts As b ON a.id <= b.id GROUP BY a.id HAVING COUNT(*)
BETWEEN 1 AND 5;
****************


And yes, I've primary keys, "id" in tblPosts and "id" in tblAuthors.
 
Hi,


That is mainly a technical problem. The WHERE clause should go after the
JOINS, and before the GROUP.

Also, it seems Authors is involved, but that you only pump data from it,
"once" the appropriate records from Posts are "top - selected", so,
probably faster to do it in two queries.

The first one implies only Posts:

============================
SELECT LAST(tblPosts.id) As ThePostID, LAST(tblPosts.posttitle),
LAST(tblPosts.posttype),
LAST(tblPosts.postdate),LAST(tblPosts.posttopic),LAST(tblPosts.authorid)

FROM tblPosts As a INNER JOIN tblPosts As b
ON a.id <= b.id GROUP BY a.id

HAVING COUNT(*) BETWEEN 1 AND 5;
============================


save that query, say qt1. Qt1 returns "records" ranked from 1 to 5 (or 67
to 98, as you wish).


Next, use that query to pump the extra data associated to the records you
restricted by qt1:


==========================
SELECT qt1.*, tblAuthors.*
FROM qt1 INNER JOIN tblAuthors
ON qt1.ThePostID = tblAuthors.ThePostID
==========================

We say that qt1 is a "virtual table", meaning you can "bring it" in the last
query, basically, it is just a cut and paste (without the ending ; and
inclosed inside ( ) ):

SELECT qt1.*, tblAuthors.*
FROM (
SELECT LAST(tblPosts.id) As ThePostID, LAST(tblPosts.posttitle),
LAST(tblPosts.posttype),
LAST(tblPosts.postdate),LAST(tblPosts.posttopic),LAST(tblPosts.authorid)

FROM tblPosts As a INNER JOIN tblPosts As b
ON a.id <= b.id GROUP BY a.id

HAVING COUNT(*) BETWEEN 1 AND 5) as qt1 INNER JOIN tblAuthors
ON qt1.ThePostID = tblAuthors.ThePostID


but that is clearly harder to read, and harder to modify. So, I strongly
suggest to use the two queries approach, in this case.


Hoping it may help,
Vanderghast, Access MVP
 
Obviously, you can't count on a specific number. You will have to store the
last number in the query for use in the Where clause of the next one. This
means that you will have to programmaticly create the "Next 10" query each
time. Are you using ASP for this?

For instance. From my NumberedQuery sample, we have this, which numbers the
records in the query:

SELECT (Select Count(1) FROM Authors A WHERE A.AuID <=Authors.AuID)
AS Sequence, Authors.AuID, Authors.AuPhone, Authors.AuName
FROM Authors
ORDER BY Authors.AuID;

To show the Top 5 from this, it would be:

SELECT TOP 5 (Select Count(1) FROM Authors A WHERE
A.AuID <=Authors.AuID) AS Sequence, Authors.AuID,
Authors.AuPhone, Authors.AuName
FROM Authors
ORDER BY Authors.AuID;

To see records 6-10, you'd have to store the value of the last record (in
this case it IS 5, but it wouldn't have to be) and use that in the Where
clause:

SELECT TOP 5 (Select Count(1) FROM Authors A WHERE
A.AuID <=Authors.AuID) AS Sequence, Authors.AuID,
Authors.AuPhone, Authors.AuName
FROM Authors
WHERE ((((Select Count(1) FROM Authors A
WHERE A.AuID <=Authors.AuID))>5))
ORDER BY Authors.AuID;

Programmatically, it would need to be something like this:
'******* begin air code *********************
Dim LastRecord as Long
Dim strSQL as String
Dim qdf as DAO.QueryDef
LastRecord = 5 '(you would need to store this programmically)

strSQL = "SELECT TOP 5 (Select Count(1) FROM Authors A " & _
"WHERE A.AuID <=Authors.AuID) AS Sequence, Authors.AuID, " & _
"Authors.AuPhone, Authors.AuName " & _
"FROM Authors " & _
"WHERE ((((Select Count(1) FROM Authors A " & _
"WHERE A.AuID <=Authors.AuID))> " & LastRecord & ")) " & _
"ORDER BY Authors.AuID;"

set qdf = CurrentDb.CreateQuery("Next10", strSQL)
'******* end air code *********************

Naturally, if you are using ASP or ADO, the code would have to change
slightly, but the idea is still the same. You would recreate the "Next10"
query each time you change a page, storing the last value from the currrent
page, and using that as a variable in creating the query. You can similarly
create a "Previous10" query by storing the first value from the current
page.

I have more examples of creating queries programmatically on my website.
Most of them start with "CreateQueries".
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Saber said:
I'm using an 'id' field as AutoNumber.
I can use the 'id' to show next 10 records, but what if
I delete a record in middle of table?
BTW- good examples in your website, thank you.


Roger Carlson said:
There is no such syntax for SQL. You'll have to use a WHERE clause that
excludes your first 10 records. How do you do that? Access doesn't have
"record numbers" as such, but you can create a query which numbers them.
On
my website (see sig below) is a small sample database called
"NumberedQuery.mdb" which shows two ways to do it. It adds a column
called
Sequence that numbers the query. You can add a TOP 10 predicate and also
a
WHERE clause Where Sequence > 10.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Saber said:
I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
Click to expand...
 
Hi Michel,

The first Query prompts me to enter tblPosts.id, tblPosts.posttitle, etc.
It doesn't reads the values from tblPosts, why?
and when I do it programmatically I get an error:
System.Data.OleDb.OleDbException: No value given for one or more required
parameters.

Thanks
 
Thanks,
after a bit changes it is:
********************************************
SELECT TOP 5 (Select Count(1) FROM tblPosts A WHERE
A.authorid <=tblAuthors.id) AS Sequence, tblAuthors.id,
tblAuthors.authorname, tblAuthors.authoremail, tblAuthors.authorurl,
tblAuthors.yim, tblAuthors.icq
FROM tblAuthors
WHERE ((((Select Count(1) FROM tblAuthors A
WHERE A.authorid <=tblAuthors.id))>5))
ORDER BY tblAuthors.id;
********************************************

I'm using ASP.Net, that's not problem to change the query variables, the
problem is query doesn't shows first 5 posts :(
It prompts me to enter A.authorid

______________________________________
 
Hi,


You are right. Since the field name can come from either "a" either
"b", we have to specify explicitly the alias we want take the data from. "a"
in this case... and I also missed the classical indentation for the GROUP BY
clause (that helps a little bit to get it formatted so it "looks" nice):

============================
SELECT LAST(a.id) As ThePostID, LAST(a.posttitle),
LAST(a.posttype),
LAST(a.postdate),LAST(a.posttopic),LAST(a.authorid)

FROM tblPosts As a INNER JOIN tblPosts As b
ON a.id <= b.id

GROUP BY a.id

HAVING COUNT(*) BETWEEN 1 AND 5;
============================



Hoping it may help,
Vanderghast, Access MVP
 
Thanks so much Roger,
The michel's command works pretty good.
but I never forget your great website.

--
Saber S.
http://maghalat.com (Persian)

Roger Carlson said:
Obviously, you can't count on a specific number. You will have to store
the
last number in the query for use in the Where clause of the next one.
This
means that you will have to programmaticly create the "Next 10" query each
time. Are you using ASP for this?

For instance. From my NumberedQuery sample, we have this, which numbers
the
records in the query:

SELECT (Select Count(1) FROM Authors A WHERE A.AuID <=Authors.AuID)
AS Sequence, Authors.AuID, Authors.AuPhone, Authors.AuName
FROM Authors
ORDER BY Authors.AuID;

To show the Top 5 from this, it would be:

SELECT TOP 5 (Select Count(1) FROM Authors A WHERE
A.AuID <=Authors.AuID) AS Sequence, Authors.AuID,
Authors.AuPhone, Authors.AuName
FROM Authors
ORDER BY Authors.AuID;

To see records 6-10, you'd have to store the value of the last record (in
this case it IS 5, but it wouldn't have to be) and use that in the Where
clause:

SELECT TOP 5 (Select Count(1) FROM Authors A WHERE
A.AuID <=Authors.AuID) AS Sequence, Authors.AuID,
Authors.AuPhone, Authors.AuName
FROM Authors
WHERE ((((Select Count(1) FROM Authors A
WHERE A.AuID <=Authors.AuID))>5))
ORDER BY Authors.AuID;

Programmatically, it would need to be something like this:
'******* begin air code *********************
Dim LastRecord as Long
Dim strSQL as String
Dim qdf as DAO.QueryDef
LastRecord = 5 '(you would need to store this programmically)

strSQL = "SELECT TOP 5 (Select Count(1) FROM Authors A " & _
"WHERE A.AuID <=Authors.AuID) AS Sequence, Authors.AuID, " & _
"Authors.AuPhone, Authors.AuName " & _
"FROM Authors " & _
"WHERE ((((Select Count(1) FROM Authors A " & _
"WHERE A.AuID <=Authors.AuID))> " & LastRecord & ")) " & _
"ORDER BY Authors.AuID;"

set qdf = CurrentDb.CreateQuery("Next10", strSQL)
'******* end air code *********************

Naturally, if you are using ASP or ADO, the code would have to change
slightly, but the idea is still the same. You would recreate the "Next10"
query each time you change a page, storing the last value from the
currrent
page, and using that as a variable in creating the query. You can
similarly
create a "Previous10" query by storing the first value from the current
page.

I have more examples of creating queries programmatically on my website.
Most of them start with "CreateQueries".
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Saber said:
I'm using an 'id' field as AutoNumber.
I can use the 'id' to show next 10 records, but what if
I delete a record in middle of table?
BTW- good examples in your website, thank you.


Roger Carlson said:
There is no such syntax for SQL. You'll have to use a WHERE clause
that
excludes your first 10 records. How do you do that? Access doesn't have
"record numbers" as such, but you can create a query which numbers
them.
On
my website (see sig below) is a small sample database called
"NumberedQuery.mdb" which shows two ways to do it. It adds a column
called
Sequence that numbers the query. You can add a TOP 10 predicate and also
a
WHERE clause Where Sequence > 10.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Saber" <saber[--AT--]maghalat.com> wrote in message
I'm using "Select TOP 10 * From tblPost" to show my first 10 posts in
a
weblog.
to create "next 10 posts" button, how can I change my command text?
I need something like: Select Records[11-20] From tblPosts
Click to expand...
Click to expand...
Click to expand...
 
Back
Top