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
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/
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
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/
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
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/
<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/