The road to using Mysql

There is often more than one way of getting what you want in Mysql. In this example we will look at records in the Emp table and bring back those with the highest sal value. The first way to get the top 3 records (which is the standard way to do it in sql) is as follows:

Code:

However in Mysql you can use the LIMIT clause to restrict how many rows your select statement brings back. This is the Mysql rough equivalent of using rownum in Oracle.

Code:

A bit easier than writing a sub-query, don't you think? Of course there are other factors such as performance and optimization that will affect your choice. But it's always good to know alternative ways of accomplishing a task.

+-------+-------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+

Late Edit!:
Remember you can also use the Limit function on a group by statement. For example:

Code:

The above code brings back the 5 top city_id with most records in that table. Hope that helps someone!