Show certain parts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using an Access database for my website, and I want to be able to show
only a certain number of posts at the time. (For example; 1-10, 11-20, 21-30,
and so on.)

If I use this SQL expression:

SELECT ID FROM Linktable ORDER BY ID DESC;

- is there some way I can get -only- the posts with the "positions" 11 to
20, in that particular expression...? A friend of mine who's used to MySQL
can't find any way to do this, but I think that would be strange.

I hope you understand my description of my problem, and I'm thankful for
every answer!

Best regards
Dacoi
 
You might be able to use the Top Predicate if you are building the query on
the fly. You can't use a parameter for the TOP predicates's value.
For instance to get 21 to 30

SELECT TOP 10 ID
FROM LinkTable
WHERE ID NOT IN
(SELECT TOP 20 ID
FROM LinkTable
ORDER BY ID)
ORDER BY ID

That assumes that ID is a unique value (primary key or primary key
candidate). It is also going to be slow with large data sets since NOT IN
is always a slow process. You could might be able to rewrite that as the
following. Which if it works will be much faster.

SELECT TOP 10 LinkTable.ID
FROM LinkTable LEFT JOIN
(SELECT TOP 20 ID
FROM LinkTable
ORDER BY ID) as T
ON LinkTable.ID = T.ID
WHERE T.ID is Null
ORDER BY ID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you for your answer! Your second piece of code worked just fine! (I
only had to change the last row to "ORDER BY LinkTable.ID") I knew about the
'Top' predicate, but I never thought of the idea of doing it that way!

Right now, the database doesn't contain many ID numbers, but it will be
updated all the time, and it will eventually contain thousands of ID:s.

I duplicated the info in the database so I had about 1500 ID:s, and it
worked fine and there was no noticeable delay in the 'process'.

Once again, thanks! Soon the website will be ready to be uploaded! =)


"John Spencer" skrev:
 
One problem you should be aware of is that you can't use that code to get
the first group of 10.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top