The true delight is in the finding out rather than in the knowing. — Isaac Asimov

Sometimes when querying MySQL I like to return a new column containing an ordered numerical sequence for each row in the result set. Here’s an explanation of how to do it.

I suppose this isn’t really needed in many, if not most, queries, but sometimes it’s just what I want. As an example, I have a users table that contains three columns: id, first_name and last name. The following query will retrieve a result set from this table where the first_name column is equal to John.

SELECT uuid
FROM users
WHERE email LIKE '%j%';

We can easily modify this query to insert an additional column to the front of the result set containing a sequential number for each record. Try this:

SELECT @ROW := @ROW + 1 AS ROW, uuid
FROM users, (SELECT @ROW := 0) r
WHERE email LIKE '%j%';

Voila! And now, how does it work? Let’s start with the FROM clause. We are specifying two sources of data. The first is the users table itself and the second is a subquery that sets a session variable called @ROW and initializes its value to zero. Being a derived table, we must specify an alias, so I used r.

In the SELECT clause, we are incrementing the value of the variable @ROW by one and selecting its value followed by the first_name column from our users table. Since @ROW started life with a value of zero, the first row of the returned result set will be one followed by two, three etc. If we wanted to start at a different value we could’ve set that value in the FROM clause.

And finally, the WHERE clause constrains our results to those whose first name is John. That was pretty easy and quite useful I think.

via Add Row Numbers to MySQL Results | DigitalWindFire.

via How To Setup VNC For Ubuntu 12 | DigitalOcean.

Background

VNC stands for Virtual Network Computing, which allows you to connect to your server remotely, and be able to use your keyboard, mouse, and monitor to interface with that server.

Step 1 – Install VNC server and XFCE 4 desktop.

To get started, we will install a VNC server on Ubuntu 12.10 x64 Server droplet. Login as root and install packages:

apt-get -y install ubuntu-desktop tightvncserver xfce4 xfce4-goodies

Step 2 – Add a VNC user and set its password.

adduser vnc
passwd vnc

If you would like to get root as user vnc you would have to add it to sudoers file. Make sure you are logged in as root:

echo "vnc ALL=(ALL) ALL" >> /etc/sudoers

Set user vnc’s VNC Server password:

su - vnc
vncpasswd
exit

This step sets the VNC password for user ‘vnc’. It will be used later when you connect to your VNC server with a VNC client:

Now you can login as user ‘vnc’ and obtain root by running ‘sudo su -‘ and entering your password:

Step 3 – Install VNC As A Service

Login as root and edit /etc/init.d/vncserver and add the following lines:

#!/bin/bash
PATH="$PATH:/usr/bin/"
export USER="vnc"
DISPLAY="1"
DEPTH="16"
GEOMETRY="1024x768"
OPTIONS="-depth ${DEPTH} -geometry ${GEOMETRY} :${DISPLAY}"
. /lib/lsb/init-functions
 
case "$1" in
start)
log_action_begin_msg "Starting vncserver for user '${USER}' on localhost:${DISPLAY}"
su ${USER} -c "/usr/bin/vncserver ${OPTIONS}"
;;
 
stop)
log_action_begin_msg "Stoping vncserver for user '${USER}' on localhost:${DISPLAY}"
su ${USER} -c "/usr/bin/vncserver -kill :${DISPLAY}"
;;
 
restart)
$0 stop
$0 start
;;
esac
exit 0

Edit /home/vnc/.vnc/xstartup and replace with:

#!/bin/sh
xrdb $HOME/.Xresources
xsetroot -solid grey
startxfce4 &

Update file permissions and allow any user to start X Server:

chown -R vnc. /home/vnc/.vnc && chmod +x /home/vnc/.vnc/xstartup
sed -i 's/allowed_users.*/allowed_users=anybody/g' /etc/X11/Xwrapper.config

Make /etc/init.d/vncserver executable and start VNC server:

chmod +x /etc/init.d/vncserver && service vncserver start

Add your VNC server to automatically start on reboot:

update-rc.d vncserver defaults

Step 4 – Connect to your VNC server with TightVNC / Chicken of the VNC

yourserver:5901