Useful Queries

we will see some use ful queries on MySQL,

1.Finding 2nd Maximum Number,

“select column A from tableA order by columnA desc limit 1,1”

2.Finding 2nd Minimum Number,

“select column A from tableA order by columnA asc limit 1,1”

3.Finding Saturday and Sunday in a year, in below code you can a procedure to extract saturday and sunday in a year,

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testa`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `testa`()
begin
declare l_functionReturnDate datetime;
declare i_TRFE_Start_Dt datetime;
declare i_TRFE_End_Dt datetime;
declare TripDate datetime;
declare l_curMonth varchar(2);
declare l_curyear varchar(4);
declare date_t char(10);
set i_TRFE_Start_Dt=now();
set i_TRFE_End_Dt=’2009-03-28 12:51:01′;
while i_TRFE_Start_Dt < i_TRFE_End_Dt do
set l_curyear=substring(i_TRFE_Start_Dt,1,4);

set l_curMonth=month(i_TRFE_Start_Dt);

set date_t=day(i_TRFE_Start_Dt);

set l_functionReturnDate=concat(l_curyear,’-‘,l_curMonth,’-‘,date_t);
select fc_GetWeekdayDate (l_functionReturnDate,’Saturday’,1);
set i_TRFE_Start_Dt = date_add(i_TRFE_Start_Dt, interval 7

day);
end while;
end$$

DELIMITER ;

******************************

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`fn_GetWeekdayDate`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetWeekdayDate`(
TheDate  Datetime,
DayName  varchar(10),
DayNum   Tinyint
) RETURNS datetime
BEGIN
DECLARE InitialDate     Datetime;
DECLARE ReturnDate      Datetime;
DECLARE Dow int;

SET InitialDate =

CASE WHEN Date_Format(TheDate, ‘%W’) = DayName THEN TheDate

WHEN Date_Format(Date_Add(TheDate, INTERVAL 1 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 1 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 2 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 2 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 3 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 3 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 4 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 4 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 5 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 5 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 6 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 6 DAY)
ELSE TheDate
END;

SET ReturnDate = CASE WHEN DayNum IS NULL or DayNum IN (0,1) Then

InitialDate
ELSE

Date_Add(InitialDate, INTERVAL (7*(DayNum-1)) DAY)
END;

/* uncomment this if you want to return null instead
of allowing crossover months

SET ReturnDate = CASE WHEN Month(TheDate) = Month(ReturnDate) THEN

ReturnDate
ELSE NULL
END;
*/

RETURN ReturnDate;
END$$

DELIMITER ;

Advertisements