Tags: subquery

How to pull back the top 3 records.

By admin on May 28, 2008 | In Daily Commute - Standard hints/tips | Send feedback »

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:

select * from emp a
  where 3 >
  (select count(*)
  from emp b where b.sal > a.sal);

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:

select * from emp
  order by sal desc
  limit 3;

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.:yes:

+-------+-------+-----------+------+------------+------+------+--------+
| 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:

select city_id, count(*) as numpeople
from people
group by city_id
order by count(*) desc limit 5;

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

Tags: limit, oracle, subquery