SHARE
TWEET

Last 10 record and Sort (PROCEDURE + Variable LIMIT)

chusiang Jan 14th, 2013 91 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * 1. select last 10 record and sort.
  3.  * 2. calc (`rowsA` + `rowsB`) / `rowsC`.
  4.  * 3. use PROCEDURE and Variable LIMIT.
  5.  */
  6.  
  7. DROP PROCEDURE IF EXISTS last_10_sort;
  8.  
  9. DELIMITER //
  10.  
  11. CREATE PROCEDURE last_10_sort()
  12. BEGIN
  13.         DECLARE record_total, record_from INT DEFAULT 0;
  14.         SET @record_total = (SELECT COUNT(record_time) FROM tableA);
  15.         SET @record_from = (@record_total - 10);
  16.  
  17.         IF @record_total < 10 THEN
  18.                 SELECT `record_time`, round(((`rowsA` + `rowsB`) / (SELECT rowsC FROM tableB WHERE bid = '1') * 100), 2) AS 'tmp' FROM tableA ORDER BY `record_time` LIMIT 10;
  19.         ELSE
  20.                 PREPARE stmt_last_10_sort FROM
  21.                 "select `record_time`, round(((`rowsA` + `rowsB`) / (select rowsC from tableB where bid = '1') * 100), 2) as 'tmp' from tableA order by `record_time` limit ?, ?";
  22.                 EXECUTE stmt_last_10_sort USING @record_from, @record_total;
  23.                 DEALLOCATE PREPARE stmt_last_10_sort;
  24.         END IF;
  25. END //
  26. DELIMITER ;
  27.  
  28. CALL last_10_sort();
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top