24 May

INSERT with a SET

This is something I did not know you could do, but you can use SET for an INSERT statement.  I guess I’m a noob.  Anyways, here is how it works in the three SQL variants that I write about.

SQL Server

INSERT INTO myjunk1
SET id = 1;

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'set'.

I think it’s time I deleted the tables I’ve named junk. Anyways, no surprise that SQL Server doesn’t like it.

MySQL

mysql> INSERT INTO myjunk
    -> SET name = 'E',
    -> number = 6;
Query OK, 1 row affected (0.04 sec)

Hmmm, another junk. Anyways, it works fine here. I’ll give more detail in the next section as I’m really writing this article for SAS.

SAS

proc sql;
CREATE TABLE work.myjunk
(
	id num
);
INSERT INTO myjunk
SET id = 2;
SELECT * 
FROM work.myjunk;
quit;

      id
--------
       2

You can also add multiple values to the table as follows:

proc sql;
CREATE TABLE work.myjunk
(
	id num
);
INSERT INTO myjunk
SET id = 3
SET id = 4
SET id = 5;
SELECT * 
FROM work.myjunk;
quit;

      id
--------
       3
       4
       5

Interestingly, you can recreate a table, albeit this is a temporary table, by running a CREATE TABLE on top of it, which is why there are only 3 values in this table. If you have multiple columns in the table, you treat them exactly like you would an UPDATE statement.

proc sql;
CREATE TABLE work.myjunk
(
	id num,
	id1 num
);
INSERT INTO myjunk
SET id = 3,
   id1 = 13
SET id = 4,
   id1 = 14
SET id = 5,
   id1 = 15;
SELECT * 
FROM work.myjunk;
quit;

      id       id1
------------------
       3        13
       4        14
       5        15

Anyways, that’s pretty much it for INSERT / SET. It’ll probably come up on the SAS Advanced test, assuming I get there, and I have to admit that I’d have blown this one.

30 Jan

Row numbering MySQL version

I haven’t posted in a bit, and to make it worse, I’m going to do a minor rerun. In this case I’m going to discuss how to set up a row counter in MySQL. Unlike SQL Server MySQL doesn’t have easy-to-use built-in row numbering functionality. So you have to cheat a little bit.

First lets look at some data. This is SIPRI military spending data for 2011 and we’re going to look at the 10 largest military expenditures. The initial query and data look like this:

mysql> SELECT *
    -> FROM spending
    -> ORDER BY spending DESC LIMIT 10;
+--------------+----------+---------+
| country      | spending | gdpperc |
+--------------+----------+---------+
| USA          |   711421 |     4.8 |
| China, P.R.  |   142859 |     2.1 |
| Russia       |    71853 |     3.9 |
| UK           |    62685 |     2.6 |
| France       |    62535 |     2.3 |
| Japan        |    59327 |       1 |
| India        |    48889 |     2.7 |
| Saudi Arabia |    48531 |    10.1 |
| Germany      |    46745 |     1.4 |
| Brazil       |    35360 |     1.6 |
+--------------+----------+---------+
10 rows in set (0.00 sec)

Now we want to add row numbers but because there isn’t any direct functionality we have to create a variable and increment that as the results are returned. We do that as follows:

mysql> use military;
Database changed
mysql> SET @row = 0;
Query OK, 0 rows affected (0.00 sec)

+------------+--------------+----------+
| Row Number | country      | spending |
+------------+--------------+----------+
|          1 | USA          |   711421 |
|          2 | China, P.R.  |   142859 |
|          3 | Russia       |    71853 |
|          4 | UK           |    62685 |
|          5 | France       |    62535 |
|          6 | Japan        |    59327 |
|          7 | India        |    48889 |
|          8 | Saudi Arabia |    48531 |
|          9 | Germany      |    46745 |
|         10 | Brazil       |    35360 |
+------------+--------------+----------+
10 rows in set (0.00 sec)

So, what we do in this query is first create a variable @row in this case, set it to zero, then all we need to do is add it to the query and increment it’s value with the @row := @row + 1. This query can also be written without the SET as follows,

mysql> SELECT @row := @row + 1 as 'Row Number', country, spending
    -> FROM spending
    -> JOIN (SELECT @row := 0) AS a
    -> ORDER BY spending DESC LIMIT 10;

The difference between this and the prior query is simply that you define the @row variable with a SELECT statement in the JOIN. I don’t know if there is any official consensus on which one to use. They both work, the first version is more readable and the latter version is a bit cleaner because it doesn’t have a SET.

27 Jan

COALESCE And NULL Ordering

NULL’s are a strange concept in that they equal nothing so how do you order them? In the case of SQL Server they come first. Other databases have options to sort them first or last and the ANSI standard says either is OK as long as you pick one. Here is a quick example,

SELECT null AS col1
UNION
SELECT 1
UNION
SELECT 2
ORDER BY col1; — This could be written as ORDER BY 1

col1
NULL
1
2

And that’s that. Actually the most relevant parts of this are that the alias can only apply to the first SELECT and the ORDER BY, just like in a regular query must appear last. This is all pretty standard stuff.

But, what if you wanted to put the NULL into the middle of this result set because, well, just because.

The easiest way to do that would be to ORDER BY a COALESCE statement as follows:

SELECT null AS col1
UNION 
SELECT 1 as 
UNION
SELECT 2
ORDER BY COALESCE(col1,1.5);
go

Except for the small problem that it returns the following errors,

Msg 207, Level 16, State 1, Line 6
Invalid column name ‘col1’.
Msg 207, Level 16, State 1, Line 6
Invalid column name ‘col1’.
Msg 104, Level 16, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Ok, that is kind of obvious. What’s interesting about that is outside of a UNION, INTERSECT or EXCEPT you can do exactly what I just tried to do and it will work. And, the ORDER BY item does, in fact, appear in the SELECT List. Fine, be that way!!!!!

So, what do we do? We use a Common Table Expression and call it a post.

WITH cte AS
(
SELECT null
UNION
SELECT 1
UNION
SELECT 2
)
SELECT *
FROM cte
ORDER BY col1

col1
1
NULL
2

I can’t really imagine using this, at least not with a NULL, but it does show how to use a SET operator with an alias or an ORDER BY and it also shows that you can order a SET by a column that isn’t in the result set, unless, you are using a UNION, INTERSECT or EXCEPT.