Showing entries 1 to 4
Displaying posts with tag: SQL Mode (reset)
Feedback Request: Enforcing SQL Mode

MySQL Server has an extensive collection of SQL modes which control a range of behavior ranging from compatibility modes for other RDBMS dialects (ANSI_QUOTES, PIPES_AS_CONCAT) to security (NO_AUTO_CREATE_USER) to explicit storage engine selection (NO_ENGINE_SUBSTITUTION) to restricting lossy implicit data conversions ( …

[Read more]
MySQL Bad Idea #573

This is MySQL’s Bad Idea #573 (after #384, which I’ve blogged about before) I’ve just had a terrible experience with a bug report from the jOOQ User Group, related to escaping of backslashes in string literals in MySQL. First, I thought to myself, whatever. SQL doesn’t escape backslashes. The only escape character within a string literal according to the early SQL standards is the quote as in quote quote. Citing from SQL-1992 (slightly simplified):

<character string literal> ::=
    <quote> [ <character representation>... ] <quote>

<character representation> ::=
    <nonquote character>
  | <quote symbol><nonquote character> ::= …
[Read more]
SQL Mode ANSI_QUOTES

I was asked today about the ANSI_QUOTES SQL mode.

According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, ANSI_QUOTES mode changes the functionality of double quotes (”) to be like the backtick (`). Normally the functionality of double quotes is more like that of single quotes (’).

You might use this when you have a table with spaces or other special characters you would like to escape, without having to use the backtick key. This is also ANSI standard SQL behavior (one of the more annoying things about Oracle is that I keep forgetting I can’t use “, only ‘).

Here is an example in the MySQL default mode — allowing ” to be more like ‘ :

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database …
[Read more]
How to select the first or last row per group in SQL

There is no "first" or "last" aggregate function in SQL. Sometimes you can use MIN() or MAX(), but often that won't work either. There are a couple of ways to solve this vexing non-relational problem.

First, let's be clear: I am posing a very non-relational problem. This is not about the minimum, maximum, top, most, least or any other relationally valid extreme in the group. It's the first or last, in whatever order the rows happen to come. And we all know rows aren't ordered -- in theory. But in practice they are, and sometimes you need the first or last row in a group. This article shows how.

Showing entries 1 to 4