You can use ORDER BY DESC command with LIMIT 1 for this since we need to delete only a single ID.Let us first create a table −mysql> create table DemoTable (    UserId int,    UserName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(100, 'John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(234, 'Mike'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(145, 'Sam'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(278, 'Carol'); Query OK, 1 ... Read More

To display message from stored procedure on the basis of conditions, let us use IF-ELSE condition −mysql> DELIMITER // mysql> CREATE PROCEDURE showMessage(value int, Name varchar(20))    BEGIN       IF(value > 100) then          SELECT CONCAT("HELLO", " ", Name);       ELSE          SELECT CONCAT("BYE", " ", Name);       END IF;       END       // Query OK, 0 rows affected (0.18 sec) mysql> DELIMITER ;Case 1 − Call the stored procedure using CALL command, when value is more than 100 −call showMessage(200, 'John');This will produce ... Read More

You can use IF() to check if data is NULL.  Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(200),    Age int ); Query OK, 0 rows affected (0.44 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Name, Age) values('John', 23); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Name, Age) values('Sam', null); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Name, Age) values('Mike', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Name, Age) values('David', 21); Query OK, ... Read More

To select first word in MySQL query, you can use SUBSTRING_INDEX(). Following is the syntax −select substring_index(yourColumnName, ' ', 1) as anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFullName varchar(40) ); Query OK, 0 rows affected (0.61 sec)Insert records in the table using insert command −mysql> insert into DemoTable(StudentFullName) values('John Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentFullName) values('Carol Taylor'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentFullName) values('Bob Williams'); Query OK, 1 row affected (0.13 sec) mysql> insert ... Read More

To select a column that is also a keyword in MySQL, you need to use backticks around the column name. As you know select is a keyword in MySQL, consider column name as select when creating a new table.Let us create a table:mysql> create table DemoTable (`select` varchar(100)); Query OK, 0 rows affected (0.53 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('Records'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('All Data'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Information'); Query OK, 1 ... Read More

To sort time in AM/PM in MySQL, you can use ORDER BY STR_TO_DATE(). Following is the syntax −select yourColumnName from yourTableName ORDER BY STR_TO_DATE(yourColumnName , '%l:%i %p');Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserLogoutTime varchar(200) ); Query OK, 0 rows affected (0.97 sec)Insert records in the table using insert command −mysql> insert into DemoTable(UserLogoutTime) values('09:45 PM'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserLogoutTime) values('11:56 AM'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserLogoutTime) values('01:01 AM'); Query OK, 1 row affected (0.17 ... Read More

To create a tab delimited select statement, you can use CONCAT() function from MySQL. Following is the syntax:select concat(yourColumnName1, "\t", yourColumnName2) AS anyAliasName from yourTableName;Let us first create a table:mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(20),    LastName varchar(20) ); Query OK, 0 rows affected (0.81 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable(FirstName, LastName) values('John', 'Smith'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(FirstName, LastName) values('Carol', 'Taylor'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(FirstName, LastName) ... Read More

Let us first create a table −mysql> create table DemoTable (    StudentId int ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(343898456); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(222333444); Query OK, 1 row affected (0.22 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-----------+ | StudentId | +-----------+ | 343898456 | | 222333444 | +-----------+ 2 rows in set (0.00 sec)Here is the query to create view ... Read More

Let us first create a table with columns for which we will calculate the difference in a new column −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    LowValue int,    HighValue int ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(LowValue, HighValue) values(100, 200); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(LowValue, HighValue) values(300, 700); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(LowValue, HighValue) values(1000, 2000); Query OK, 1 row affected (0.13 sec)Following is the query to ... Read More

To perform custom sort by field value in MySQL, use the FIELD() method in ORDER BY. Let us first create a table:mysql> create table DemoTable (StudentId int); Query OK, 0 rows affected (0.58 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(110); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(70); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(120); Query ... Read More