When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: As always, we’re using the Sakila database for this example. This is rather straightforward. It will give us page 2 out of N pages, with a page size of … Continue reading Calculating Pagination…
#derived tables
4 posts
11 Mar 2021
2 Jun 2015
You probably know about “ordinary views” already, but I’m sure you’ll find one or two things in this article that you haven’t thought about in this way yet… What exactly are SQL views? Views in SQL are a means of treating complex queries in the same way as “ordinary” tables. In fact, SQL is all … Continue reading What Exactly…
24 Jun 2014
Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: The question really was … Continue reading Should I…
7 May 2014
I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order. They wrote They got CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 GE526OTACCD3ANPH-149839 NI564FAACJSFANPH-162605 They wanted CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 NI564FAACJSFANPH-162605 GE526OTACCD3ANPH-149839 Very often, according to your business rules, sorting orders are not “natural”, as in numeric sortin