Sunday, April 10, 2022

SMS: Quota Queries

Note: "Setting Matters Straight" (SMS) is a new format: I post on LinkedIn an online Q&A involving data fundamentals that I subsequently debunk in a post here. This is to encourage readers to test their foundation knowledge against our debunking here, where we confirm what is correct and correct what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). 

Q: “How do you return the most recent record in SQL?”

A: “There are many ways of doing it. I would suggest (first thing came to my mind):
Select Top 1
from YourTable
order by TablePrimaryKey Desc;”
A: “If you mean "the last inserted record which has no datetime stamp field" ... you have a few options.
  • If you cannot use date/time -- your next best bet would be an auto-increment/sequence field, which assigns increasing numbers to each inserted record.
  • If that’s not available, you would have to rely on business logic e.g. order # or some such.
Some vendors, like Oracle, provide ROWID pseudocolumn for each record which might help in some quick’n’dirty cases -- it is not guaranteed to be sequential but could be (e.g., when table has had no DELETE operations).”

If you don't know, I set matters straight


DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.


03/25 SMS: Keys and Indexes

03/18 SMS: Entities and Records

03/06 TYFK: Relationships -- Uniqueness and Attribute Constraints

- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my AllAnalytics columns no longer work. I re-published only the 2017 columns @dbdebunk, and within them links to sources external to AllAnalytics may or may not work.

I deleted my Facebook account. You can follow me @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.

Some data practitioners know of (though not necessarily understand) the operations of the relational algebra (RA) from set theory (restrict, project, join, union), but what about quota queries (qq) -- do they
constitute a relational operation to be included in the relational algebra (RA)? In a chapter in my third book I:

  • Conceptualized a qq as a relation partition operation;
  • Suggested a simple and intuitive syntax for it;
  • Assessed critically SQL support;

The example I used in the book was the qq "What are the top x sales?" against the relation SALES that had a SALES_AMT attribute. But "the last inserted record which has no datetime stamp field" mentioned in the first answer above indicates tat there is no date attribute equivalent to SALES_AMT. This is a good opportunity to illustrate the practical importance of data fundamentals, disregard of which have consequences to which data practitioners are oblivious.


RA support is provided by a true RDBMS and its advantages (soundness, data independence, declarative and decidable data sublanguage) materialize if and only if databases are RDM-compliant. According to the new understanding of the RDM -- derived from McGoveran's interpretation of Codd's work -- this means that:

  • Database design adheres jointly to three principles:
    - Principle of Expressive Completeness (POEC);
    - Principle of Representational Parsimony (PORP);
    - Principle of Orthogonal Design (POOD)
    which produces a set of independent base relations (POOD) in 5NF minimally necessary (PORP) to derive via RA all other relations meaningful to user applications (POEC). Base relations are independent in the sense that none is derivable from the others and in 5NF in the sense that each represents a group of entities of a single type. 

One quick and dirty test of the relational fidelity of a data management system -- the combination of a DBMS and the databases it manages -- was Codd's set of 12 (actually 13) rules.  Though problematic and no longer used, the first three are valid and can be used to illustrate the practical consequences of common disregard for data fundamentals, here POEC and Rule #1 in particular.

#0 Foundation Rule: For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.

#1 Information Principle: All information in a relational database is represented explicitly and in exactly one and only one way -- by values in relations.

#2 Guaranteed Logical Access: Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of relation name, primary key value and attribute name.
"Relational capabilities" means primarily RA -- the manipulative component of RDM that takes one or more database relations as input and outputs relation(s) -- which sort of is the RDM. Information that is not
represented as explicit values in relations -- either not at all (POEC violation), or represented non-relationally -- is inaccessible to (hidden from) the DBMS and, thus, the RA, defeating relational capabilities in violation of the rules, with complications and loss of relational benefits.


We shall ignore the first answer -- it's nonsense (there is some nonsense in the second answer too).

Records and fields is physical terminoloy -- at the logical (relational) level there are tuples and attributes -- indicating logical-physical confusion (LPC).  If the chronology of logical tuple insertion is meaningful, it can be either as data or as meta-data. If the information is absent, both cases are POEC and Rule #1 violations: in the former tuples are incomplete, in the latter the database catalog (which is a relational database itself) is incomplete. The only correct relational solution is to jointly adhere to database design principles -- all information, be it data or meta -- should be represented explictly as values in relations -- be it in the database or catalog.

True RDBMSs and proper database design would have enabled the relation partition operation, obviating the question. But SQL DBMSs are not RDBMSs and database designers do not adhere to the principles, which forces non-relational/logical problematic workarounds.

No comments:

Post a Comment

View My Stats