Counting Distinct Records in SQL

The SQL language and its depths... Some of you readers might be aware of MySQL's capability of counting distinct records through the COUNT() aggregate function. The MySQL documentation reads: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values. In other words, you can count distinct first and last names very easily: SELECT … Continue reading Counting Distinct Records in SQL

Row value expressions and the BETWEEN predicate

Now this is a simple example of how SQL clause simulation can get nasty if you want to make use of some more advanced SQL clauses that aren't supported in all databases. Consider the following predicate and equivalent transformations thereof: The BETWEEN predicate The BETWEEN predicate is a convenient form of expressing the fact that … Continue reading Row value expressions and the BETWEEN predicate

The IS DISTINCT FROM predicate

The SQL-1999 standard specifies a useful IS DISTINCT FROM predicate, that behaves slightly different from the regular not equal comparison predicate. Here is its simple definition: 8.13 <distinct predicate> Format <distinct predicate> ::= <row value expression 3> IS DISTINCT FROM <row value expression 4> <row value expression 3> ::= <row value expression> <row value expression … Continue reading The IS DISTINCT FROM predicate

Publicly available SQL standard documents

For your reference, here is a list of publicly available SQL standard documents. Some are very late draft versions, where the final version is closed source and has to be purchased from ISO or ANSI directly: SQL-92 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt SQL:1999 http://web.cs.ualberta.ca/~yuan/courses/db_readings/ansi-iso-9075-2-1999.pdf SQL:2003 http://www.wiscorp.com/sql_2003_standard.zip SQL:2008 (preliminary) http://www.wiscorp.com/sql200n.zip The links were taken from here: http://wiki.postgresql.org/wiki/Developer_FAQ