QuickTip: Limiting the number of rows returned by MySQL. Pagination

If you would like to get a row num, you would have to do something like this

SELECT @rn:=@rn+1 AS rank, itemID, ordercount
FROM (
  SELECT itemID, COUNT(*) AS ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC
) t1, (SELECT @rn:=0) t2;

for getting it in the right order

or simply

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC;

For limiting the number of rows however, there is the LIMIT keyword

[bash]

mysql> select * from CODE_PANEL_DETAIL limit 1,4;
+———————-+—————+——+—————————–+——————–+
| CODE_PANEL_DETAIL_ID | CODE_PANEL_ID | CODE | CODE_TYPE | CODE_SEARCH_STRING |
+———————-+—————+——+—————————–+——————–+
| 2 | 3 | 010 | Age appropriateness of Toys | +toy |
| 3 | 3 | 021 | Employee Helpfulness | age |
| 6 | 3 | 020 | Aisles | +toy |
| 7 | 3 | 030 | Large Variety | +toy |
+———————-+—————+——+—————————–+——————–+
4 rows in set (0.00 sec)

mysql> select * from CODE_PANEL_DETAIL limit 2;
+———————-+—————+——+—————————–+——————–+
| CODE_PANEL_DETAIL_ID | CODE_PANEL_ID | CODE | CODE_TYPE | CODE_SEARCH_STRING |
+———————-+—————+——+—————————–+——————–+
| 1 | 3 | 001 | Oranization | +toy |
| 2 | 3 | 010 | Age appropriateness of Toys | +toy |
+———————-+—————+——+—————————–+——————–+
2 rows in set (0.00 sec)
[/bash]

Posted in: Java