Beautiful code

By admin on Aug 9, 2008 | In Daily Commute - Standard hints/tips, Formula 1 - More advanced stuff | Send feedback »

A bit of a delay since my last entry. I've started a new job which uses SQLServer (boo!) so my use of Mysql has been very small lately.
Anyway, let's take a little look at the group by clause. One of the most common ways I use it is to bring back records that are duplicated more than once. For example:

Code:

mysql> select ename, job, deptno,count(*) from emp group by ename, job, deptno having count(*) > 1

;
+-------+----------+--------+----------+
| ename | job | deptno | count(*) |
+-------+----------+--------+----------+
| WARD | SALESMAN | 30 | 2 |
+-------+----------+--------+----------+
1 row in set (0.00 sec)

Now how would you bring back (to delete or update) the latest insert of this duplicated record? In this case there are 2 records, one with empno of 7521, the latest record has the empno 8000. The following code (in my humble opinion) is very elegant, and is perhaps my favourite bit of sql I've seen.. thanks to this book sqlcookbook

Code:

mysql> select * from emp
    -> where empno not in (select min(empno)
    ->                     from emp
    ->                     group by ename, job, deptno);

+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+------+------+--------+
| 8000 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
+-------+-------+----------+------+------------+------+------+--------+
1 row in set (0.01 sec)

Here's another way of using the group by clause (again using subqueries) to bring back the employees with the highest and lowest salary in the dataset.

Code:

mysql> select ename, job, deptno
    -> from emp
    -> where sal in ((select min(sal) from emp),
    ->               (select max(sal) from emp));

+-------+-----------+--------+
| ename | job | deptno |
+-------+-----------+--------+
| SMITH | CLERK | 20 |
| KING | PRESIDENT | 10 |
+-------+-----------+--------+
2 rows in set (0.10 sec)

Now that, to me is also beautiful code.
Enjoy your coding - Until next time!