1.How To Create an SNo In MySql – Similar to row_num in MSSQL
SELECT @rownum:= @rownum+1 AS Sno FROM Personal_details p,(SELECT @rownum:=0) r;
How to Subtract 7 Day from Now
SELECT DATE_SUB(NOW(),INTERVAL 7 DAY)</pre>
How to View Active Connections & Process
SHOW PROCESSLIST;
How to Get Balance
Input Table
Query 1
SELECT date, SUM(CASE WHEN TYPE = 'cash_in' THEN amount END) AS cash_in, SUM(CASE WHEN TYPE = 'cash_out' THEN amount END) AS cash_out, SUM(CASE WHEN TYPE = 'cash_in' THEN amount ELSE -amount END) AS balance FROM tbSample4 WHERE type IN ('cash_in', 'cash_out') GROUP BY date(date)
Query 2
SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance FROM (SELECT currency_id, SUM(AMOUNT) cash_in FROM tbSample4 WHERE type = 'cash_in' GROUP BY currency_id) a, (SELECT currency_id, SUM(AMOUNT) cash_out FROM tbSample4 WHERE type = 'cash_out' GROUP BY currency_id) b WHERE a.currency_id = b.currency_id
Output Table
2.How to Make Selected Names at the Top of List
SELECT Names FROM tbNames</pre> <strong>Input Table</strong> <img src="http://codeatelier.files.wordpress.com/2012/07/6.jpg" alt="" width="70" height="235" /> <strong>Output</strong> <strong>Query1</strong> [code lang="sql"] SELECT Names FROM tbNames ORDER BY CASE WHEN names IN ('Chandiran', 'Suryan') THEN 0 ELSE 1 END</pre>
Query2
SELECT * FROM tbNames ORDER BY Names IN ('Heman','superman') DESC;</pre> <img src="http://codeatelier.files.wordpress.com/2012/07/7.jpg" alt="" width="75" height="241" />
3.How To Return Row in Column as Comma Separated Value CSV
SELECT GROUP_CONCAT(Name) FROM tblNames WHERE Id IN(15,16,17,18);
4.How Add OR Condition in Mysql case
CREATE TABLE tblConfirmationStatus (Confirm_Status TINYINT); INSERT INTO tblConfirmationStatus Confirm_Status VALUES (1), (0), (1), (null), (0), (1), (null);
Required Output
ConfirmStatus
Confirmed Not Confirmed Confirmed Not Confirmed Not Confirmed Confirmed Not Confirmed
Query 1
SELECT CASE WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' ELSE 'Confirmed' END AS ConfirmStatus FROM tblConfirmationStatus;
Query 2
SELECT CASE WHEN Confirm_Status > 0 THEN 'Confirmed' ELSE 'Not Confirmed' END AS ConfirmStatus FROM tblConfirmationStatus;
MySQL Query To Skip Days – Sunday as Below
CREATE TABLE Patients_Appointment_List(Patent_Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,Patient_Name VARCHAR(255),appointment_Date DATE); INSERT INTO Patients_Appointment_List (Patient_Name, appointment_Date) VALUES ('Mugil', 20120730), ('Vinu', 20120730), ('Madhavan', 20120729), ('Gopal', 20120730), ('Shivaji', 20120731); SELECT Patient_Name FROM Patients_Appointment_List WHERE CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY ELSE appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY END
Output as on 20120728
Patient_Name
Mugil Vinu Madhavan Gopal