SQL SELECT LAST does not return the last record

Posted on 2011-10-19
Last Modified: 2012-06-21
I have some SQL running on an ASP page that uses the SELECT LAST query, to select the last record of a few fields where a condition on a certain field/s is true (from an MS Access database).

Up until recently, this was working fine. But I am now seeing some odd behaviour. The query is not selecting the last record for the given condition, neither is it necessarily selecting the first. Sometimes it may be the second (based on the ID field - primary key) record that meets the condition, etc.

As a workaround, I have used SELECT MAX on the ID field where the conditions are true, and run another query based on this result but this is a messy way of doing this based on the rest of my ASP page.

Any help appreciated!
Question by:noooodlez
    LVL 39

    Accepted Solution

    select top 1 * from table_name order by columnname desc

    if the columnname is id of the table

    if you want to add your condtion put the where condition

    select top 1 * from table_name
    where col_anme= 'ss'
    order by columnname desc

    Author Closing Comment

    Spot on, worked perfectly. Many thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now