select last 4 records added to db but not show the last one

[ndeeley]

Hello,

This is going to sound strange but I have a webpage that displays a form showing the last record added. This is because further records share a lot of the same data, so only a few fields have to be changed. I use the max(ID) to do this:

<cfquery name="lastid" datasource="swwdrawings">
select        *
from        tblDrawingsData
where        ID = (select max(ID) from tblDrawingsData where AddedBy = '#username#')
    </cfquery>
 

I also have a side bar that shows the last 5 records so these can be easily editable (a link fires the ID of the record to the form):

<cfquery name="lastfive" datasource="swwdrawings" maxrows="5">
select        *
from        tblDrawingsData
where        AddedBy = '#username#'
order by    ID desc
</cfquery>
 

Obiviously this also shows the last (max) ID which already appears in the main form. How do I show the penultimate 5 records before the last one to be added? I have tried this but it doesn't appear to work:

<cfquery name="lastfive" datasource="swwdrawings" maxrows="5">
select        *
from        tblDrawingsData
where        AddedBy = '#username#' and ID <> (select max(ID) from tblDrawingsData where AddedBy = '#username#')
order by    ID desc
</cfquery>
 

Thanks!
Neil

[acoder]

Which database are you using? You could use limit in MySQL.

Another alternative is to take the ID returned by the first query and use it in the second query.

[ndeeley]

I'm using a bog standard Access database. Will limit work with that? Must admit I have no idea how to use it!

[acoder]

In Access, you can use select top 5. This will grab the first 5 (descending, so it'd be the last 5), but if you exclude the last one, then it'd be the penultimate 5. In your main query where you get the last record, store the ID to use in this query.

[ndeeley]

Thanks acoder - I`ll give it a go!

Neil