4 ms·
I was pretty bewildered by the convoluted way they posited for selecting a subrange of rows, but then I googled around and learned that LIMIT is a nonstandard M
by 4bpp 10y ago
I was pretty bewildered by the convoluted way they posited for selecting a subrange of rows, but then I googled around and learned that LIMIT is a nonstandard MySQL extension. Huh.
(Apparently, while LIMIT n,k is MySQL-only, MySQL, PostgreSQL and sqlite all support LIMIT k OFFSET n. So I assume this blog post is only really relevant for people who live in a Microsoft ecosystem?)
- rjbwork 10y agoWas talking to another person at work who is non technical but starting to dip his toes in the waters. He asked how to do just this and I had to go on a fairly long explanation, prompted by repeated questions, into the SQL standard, how that want added until near 3 decades after SQL's invention, vendor specific extensions and syntaxes etc. It's a pretty bizarre situation.
- Someone 10y agoIt took a while, but SQL Server 2012 introduced OFFSET/FETCH: SELECT * FROM items ORDER BY key OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY (https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx https://technet.microsoft.com/en-us/library/gg699618(v=sql.1...)
- siddboots 10y agoPart of it may be due to a deep problem with OFFSET in that although it can be convenient, there is no reliably efficient way to implement it. In fact, the only way it can possibly be efficient is if your data is fully ordered, and that ordering is indexed. As a result, I find it's better to just stick to the following pattern: select * from data where data.indexed_column > last_seen order by data.indexed_column desc limit 100