SQL Query & Precedure in MySQL

MYSQL Group By Query with n rows returning per group

Before executing this query please read about GROUP_CONCAT and FIND_IN_SET from mysql site.

In my case i have a table called testtable with fields mobilenumber,location with dummy data.

SELECT testtable.* FROM

testtable INNER JOIN (

SELECT location, GROUP_CONCAT(number) grouped_number

FROM testtable

GROUP BY location) group_max

ON testtable.location = group_max.location

AND FIND_IN_SET(number, grouped_number) <=2

ORDER BY testtable.number DESC;

Procedure in MySQL

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘CALLFIRE’ . ‘fund_check’$$

CREATE PROCEDURE ‘Callfire’. ‘fund_check’ (IN UID varchar (6), IN DUR INT, IN PHNO Varchar (10))

BEGIN

DECLARE PHNUM, CHK, M_NUM INT;

set PHNUM=O, CHK=O, M_NUM=O;

set PHNUM =substr (PHNO,1,1);

IF PHNUM = 9 THEN /*MOBILE*/

set M-NUM =substr (PHNO,1,5);

SELECT COUNT (*) INTO CHK FROM MSISDN WHERE MSISDNLIST=M_NUM ;

IF CHK <> O THEN /*LOCAL MOBILE*/

SET @s=CONCAT(‘UPDATE callrate set available_fund=available_fund-(CEIL(‘,DUR,’/local_mobile_pulse)*local_mobile_rate) where uniqueid=’,UID);

PREPARE stmpt FROM @s;

EXECUTE stmpt;

ELSE /*STD MOBILE*/

SET @s=CONCAT(‘UPDATE callrate set

available_fund=available_fund-(CEIL(‘,DUR,’/std_mobile_pulse)*std_mobile_rate) where uniqueid=’,UID);

PREPARE stmt FROM @s;

EXECUTE stmt;

END IF;

ELSEIF substr(PHNO,1,2)=11 THEN /*LOCAL LANDLINE */

SET @s=CONCAT(‘UPDATE callrate set

available_fund=available_fund-(CEIL(‘,DUR,’/local_landline_pulse)*local_landline_rate) where uniqueid=’,UID);

PREPARE stmpt FROM @s;

EXECUTE stmpt;

ELSE /*STD landline */

SET @s=CONCAT(‘UPDATE callrate set

available_fund=available_fund-(CEIL(‘,DUR,’/std_landline_pulse)*std_landline_rate) where uniqueid=’,UID);

PREPARE stmpt FROM @s;

EXECUTE stmpt;

END IF;

END$$

DELIMITER ;

HOW TO CREATE EVENT IN MYSQL?

Step 1 : Is the mysql server configuration activation for event to work. We can also put this in the my.cnf file.

Step 2 : ON SCHEDULE EVERY 5 SECOND means it will execute every 5 seconds we can change that to hour,day,month or minute.

Step 3: Will start the EVENT from the time stamp provided. In the following case I have put NOW() you can put any date. It will also alter the eventfrom running every 5 second to every 1 minute.

=============== Step 1 ==============================

SET GLOBAL event_scheduler=ON;

=============== Step 2 ==============================

DELIMITER $$

DROP EVENT evt_createarchive$$

CREATE EVENT `sbm`.` evt_createarchive `

ON SCHEDULE EVERY 5 SECOND

DO

BEGIN

INSERT INTO test_archive(id,NAME,STATUS) SELECT id,NAME,STATUS FROM test1 WHERE STATUS=1;

DELETE FROM test1 WHERE STATUS=1;

END$$

DELIMITER ;

=============== Step 3 ==============================

ALTER EVENT evt_deleterow ON SCHEDULE EVERY 1 MINUTE STARTS NOW();