I must be losing my touch with SQL statements because I can’t figure out the ideal way to select the last 100 records of a table by using one query. The main consideration would be that the records are returned in ascending order based on their primary key, which means the following query doesn’t do the trick:

SELECT RecordId 
FROM Table 
ORDER BY RecordId DESC 
LIMIT 100

The above query would select the last 100 records, but they would not be returned in the order they were inserted into the table. And then, there is a traditional two query approach where first you select the count:

SELECT COUNT(RecordId) AS recordCount 
FROM Table

Let’s say the count is 300. Using our server side code, we can subtract 100 and use the LIMIT keyword.

SELECT RecordId
FROM Table
LIMIT 200, 100

So that would give us the last 100 records, but two queries are needed. How can this be done in one query? Here is what I could come up with:

SELECT RecordId
FROM Table
WHERE RecordId
IN (
    SELECT RecordId 
    FROM Table 
    ORDER BY RecordId DESC 
    LIMIT 100
)
ORDER BY RecordId ASC

The subquery finds the last 100 records and the main query compares against it while still keeping the order in tact. This should work in some versions of MySql, but problems could be encountered due to the LIMIT keyword in the subquery. And the other questions that this poses are how well does this query perform and are there any other blindingly obvious alternatives that I’m overlooking?

HTML Form Builder
Ryan Campbell

Select Last 100 Records by Ryan Campbell

This entry was posted 4 years ago and was filed under Notebooks.
Comments are currently closed.

· 24 Comments! ·

  1. zsp · 4 years ago

    just a quick idea (haven’t tested it): SELECT t2.RecordId FROM Table t1, Table t2 where t1.RecordId = t2.RecordId ORDER BY t1.RecordId DESC, t2.RecordId ASC LIMIT 100

  2. Mark · 4 years ago

    Two thoughts while reading this:

    1. You didn’t specify MySQL. When I saw this in RSS, and as I started reading it, using “TOP 100” popped into my head.
    2. I typically design every database table with CreatedDate and ModifiedDate fields. The first defaults to the current timestamp on INSERT. Would that have helped in sorting and grabbing the subset?
  3. Duncan Beevers · 4 years ago

    Still technically two queries…

    SELECT RecordID FROM Table WHERE RecordID IN (

    SELECT RecordId FROM Table ORDER BY RecordId DESC LIMIT 100

    ) ORDER By RecordId

  4. Ryan Campbell · 4 years ago

    It is still two, but without any of the overhead from a programming environment that it takes to actually call two separate queries.

  5. Casey · 4 years ago

    You could use a subquery:

    SELECT foo FROM (SELECT foo FROM myTable ORDER BY id DESC LIMIT 100) as last100 ORDER BY id

  6. Henrik Pejer · 4 years ago

    Often are two simple queries faster than one complex.

    I’d say: stick with two simple and fast queries instead of trying to solve the problems with subqueries.

    It’ll make the application faster and most likely easier for you to read when you go back over the code in 4 months to fix something or perhaps add a function or two… ;)

  7. Peter · 4 years ago

    I go with Mark. I also always include timestamps for creation and last mod.

  8. erik · 4 years ago

    There is always the temp table method. CREATE TEMPORARY TABLE bar (m INT) SELECT n FROM foo ORDER BY n DESC LIMIT 100;

    SELECT * FROM bar ORDER BY m ASC;

  9. Rob · 4 years ago

    I’m not sure if there’s a better way to get MySQL to do it than the two tables or the subquery, but what’s keeping you from just iterating over the records backwards? for($i = $records.length - 1;$i >= 0; $i—) isn’t any harder than for($i = 0; $i < $records.length; $i++). Seems like you’re over-thinking the problem.

  10. Ryan Campbell · 4 years ago

    Rob, that would work just fine if you had an array containing all of the records. But if your table has 10,000+ records, there is no need to load them all. It would be much better to just grab the last 100.

  11. Luke · 4 years ago

    I’m on board w/ Rob here. Rather than incur the DB IO overhead of a second query to get an asc sequence, just ORDER BY RecordId DESC LIMIT 100, then iterate through the result set backwards. Alternately, if you need the array for more than a single iteration, reverse it. Many languages have array reverse routines, so you don’t even have to bother doing it manually.

    If the problem is that RecordIds are reused after record deletion, your only bet is to use Mark’s suggestion of having a CreateDate column to use in the ORDER BY.

  12. nathan m. · 4 years ago

    re: luke. So instead of incurin the db “overhead” of a second query, you’ll incure the cost of the network traffic to copy perhaps 100x the data and the server overhead to hold that data in memory, boxing and unboxing (you would be using reference types, ja?) and then processing power to do a sort that Sql would have been doing anyway had you let sql do it’s job? yeesh.

  13. Ryan Campbell · 4 years ago

    Makes sense Luke that you still only grab 100, and then reverse them. That helps with the performance issue. But still, placing that logic in the code doesn’t seem ideal. Let’s say you have something generic like a query API. Somoene uses your API, and your nice clean functions always return an array of data. Now logic will be needed in the code to do the reversal under certain circumstances, rather than keeping it consistent and letting SQL handle everything.

  14. Lars · 4 years ago

    Hi Ryan, I think Rob has the right idea. Your first query

    
    SELECT RecordId 
    FROM Table 
    ORDER BY RecordId DESC 
    LIMIT 100
    

    returns a subset (last 100) of all records in the given table. Now if you want to work with the individual records in the order that they were entered, you just have iterate through this result set in reverse order…

  15. Lars · 4 years ago

    Oops, should’ve refreshed before posting my comment, you posted your reasons for wanting to keep the ordering logic in SQL, while I was reading and formulating an answer. Nevermind…

  16. Kevin · 4 years ago

    Hi Ryan,

    Why is the root cause of the specification that the data must be in ascending order? What if the end user wanted to display in descending order? If you provide an API, why not 2 methods: getLastUpdatedDesc() and getLastUpdatedAsc() where the latter uses the former to deliver the results? Then your original query is the most optimal.

    If you want to embed the logic at the highest level, a view or stored procedure would be your best bet, but this eliminates Mysql < 5. There really isn’t a difference between code and SQL if both are used at the same tier.

    Kevin

  17. Mislav · 4 years ago

    This is insane. You had it in the first query, just make a reverse loop in the app.

  18. Mislav · 4 years ago

    Seems like I also didn’t read all the comments at first.

    Ryan, your reasoning is … strange. If you’re building an API on top of SQL, but refuse to have application logic in it and try to keep everything in the database, then we’re not talking about 3-tiered design. So if you’re building an API that is in fact pure SQL, then let SQL be your API.

  19. Ryan Campbell · 4 years ago

    I can see everyones reasoning for a reversal of the array is no big deal, but in a perfect world wouldn’t we still have it in the query? For example, let’s say we have a function that loops through an array and makes an ordered list. Then we have a function that takes an SQL query and hits the DB and loads the given array.

    function loadData($sql) {
        $data = queryDb($sql);
        drawList($data);
    }function drawList($data) {
        // loop through array and make list
    }
    

    In order to make those function generic, a second parameter would have to be added to loadData to determine if a reversal should be done. Wouldn’t this be easier if it was all handled in the SQL? Maybe I’m misunderstanding something … correct me if I’m wrong.

  20. Jim · 4 years ago

    Off topic: Is it just me, or does anyone else find this comment layout hard to follow? Do I follow it from the left down, and then rightside down? Or do I alternate from the left to the right? Or is it some other way?

    Very confusing. Not a design decision I would have expected from this kind of site.

  21. Peter · 4 years ago

    I agree with Mark. Have created and modified fields (as needed). You are using the pk for ordering the records here, and imho that is a misuse of the PK.

  22. Different Kevin · 4 years ago

    Agree completely with Him

  23. Different Kevin · 4 years ago

    Him=Jim :D

  24. Murtaza · 3 years ago

    I agree with Ryan, Its better to keep your code clean and easier to understand and let Sql do the job when it can do it easily and efficiently and without creating a mess on application logic