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?
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
Two thoughts while reading this:
Still technically two queries…
SELECT RecordID FROM Table WHERE RecordID IN (
SELECT RecordId FROM Table ORDER BY RecordId DESC LIMIT 100
) ORDER By RecordId
It is still two, but without any of the overhead from a programming environment that it takes to actually call two separate queries.
You could use a subquery:
SELECT foo FROM (SELECT foo FROM myTable ORDER BY id DESC LIMIT 100) as last100 ORDER BY id
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… ;)
I go with Mark. I also always include timestamps for creation and last mod.
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;
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.
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.
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.
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.
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.
Hi Ryan, I think Rob has the right idea. Your first query
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…
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…
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
This is insane. You had it in the first query, just make a reverse loop in the app.
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.
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.
In order to make those function generic, a second parameter would have to be added to
loadDatato 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.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.
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.
Agree completely with Him
Him=Jim :D
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