MySQL Forums
Forum List  »  Quality Assurance

Re: MySQL help.
Posted by: deepak m
Date: March 27, 2012 06:44AM

hi

i wrote an procedure to do this task

i have created the table as
CREATE TABLE price_comp(pk INT auto_increment primary key,date_col DATE, price INT);

inserted values

select * from price_comp;
pk date_col price
1 2012-03-27 100
2 2012-03-25 90
3 2012-03-24 91
4 2012-03-23 88
5 2012-03-22 70
6 2012-03-20 75

now wrote a procedure as


DELIMITER //

DROP PROCEDURE IF EXISTS price_comp_proc//


CREATE PROCEDURE price_comp_proc()
BEGIN

DECLARE max_date DATE DEFAULT 0;
DECLARE max_price INT DEFAULT 0;

SET max_date=(SELECT MAX(date_col) FROM price_comp);
SET max_price=(SELECT price FROM price_comp WHERE date_col=max_date ORDER BY price DESC LIMIT 1);

CREATE TEMPORARY TABLE final_temp(pk_t INT,date_col_t DATE, price_t INT);

REPEAT
IF EXISTS (SELECT 1
FROM price_comp
WHERE date_col=(SELECT MAX(date_col)
FROM price_comp
WHERE date_col < max_date)
AND price<max_price) THEN
INSERT INTO final_temp
SELECT * FROM price_comp WHERE date_col=max_date;


END IF;
SET max_date=(SELECT MAX(date_col) FROM price_comp where date_col<max_date ORDER BY price DESC LIMIT 1);
SET max_price=(SELECT price FROM price_comp WHERE date_col=max_date ORDER BY price DESC LIMIT 1);


UNTIL max_date IS NULL
END REPEAT;
SELECT * FROM final_temp;
DROP TABLE final_temp;

END//


I GOT RESULT AS

CALL price_comp_proc();

pk_t date_col_t price_t
1 2012-03-27 100
3 2012-03-24 91
4 2012-03-23 88



Regards
Deepak M

Options: ReplyQuote


Subject
Views
Written By
Posted
2094
March 19, 2012 08:44PM
Re: MySQL help.
1278
March 27, 2012 06:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.