Forums

This topic is locked

Only the final 8 records

Posted 26 Nov 2001 19:37:21
1
has voted
26 Nov 2001 19:37:21 Mark Roberts posted:
I note the 'Last 25 Records' thread below, but trying to use LIMIT with Access produces a syntax error. How can I restrict my rs to only the last 8 records using SQL on an Access database?

Many Thanks,

Mark

Replies

Replied 27 Nov 2001 12:08:31
27 Nov 2001 12:08:31 Mark Roberts replied:
Don't you just love it when you answer your own queries (pardon the pun)? <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

The Access equivalent of LIMIT is TOP. Like this:

SELECT TOP 8 *
FROM table
ORDER BY itemPriority DESC

Now...if anyone can tell me how to choose all records EXCEPT the final 8, I'd be a very happy webmonkey indeed...

Mark
Replied 27 Nov 2001 21:34:08
27 Nov 2001 21:34:08 Joel Martinez replied:
Hmm, I can say that I've never had to choose everything "But" the bottom <b>X</b>

perhaps if you explain why you would need such a query, and what the table looks like.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
Replied 27 Nov 2001 23:02:55
27 Nov 2001 23:02:55 Mark Roberts replied:
The table itself is nothing special. It contains a series of news articles.

My client has decided that they want the 8 most recent articles to be 'current'. The remaining articles, however many there are, they want to appear in an 'archive'.

So, the records are split into: the eight most recent; and the rest.

Many thanks, Joel - hope this makes sense!

Mark
Replied 28 Nov 2001 15:49:13
28 Nov 2001 15:49:13 Joel Martinez replied:
oh, well that's easy (assuming you have some sort of "Dateposted" field...

just order the query by DatePosted DESC, and select TOP 8

SELECT TOP 8 * FROM articles ORDER BY dateposted DESC

And that should give you the result you want (I do the same thing on my site www.codecube.net , although I only use the top 6, but you get the idea)

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
Replied 28 Nov 2001 15:58:58
28 Nov 2001 15:58:58 Mark Roberts replied:
Eek! I'm sure I'm missing something obvious here (it won't be the first time <img src=../images/dmxzone/forum/icon_smile_tongue.gif border=0 align=middle>, but that statement just gives me the 8 most recent...I also want a statement that gives me everything BUT the 8 most recent...the first statement for the 'Current' page, and the second statement for the 'Archive' page...

Many Thanks,

Mark

Replied 28 Nov 2001 16:08:33
28 Nov 2001 16:08:33 Joel Martinez replied:
oh oh oh , I'm sorry, I misunderstood. I think this can be accomplished with a subquery (unless you're using mysql which doesn't support them)<pre id=code><font face=courier size=2 id=code>SELECT * FROM articles WHERE articleID NOT IN
<b>(SELECT TOP 8 articleID FROM articles ORDER BY dateposted DESC)</b>
ORDER BY dateposted DESC</font id=code></pre id=code>

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

Reply to this topic