To select max of mixed string/int column, you need to use substring() function. The syntax is as follows:SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table StringIntMixHighestDemo    -> (    -> InvoiceId int NOT NULL AUTO_INCREMENT,    -> InvoiceNumber varchar(20),    -> PRIMARY KEY(InvoiceId)    -> ); Query OK, 0 rows affected (0.65 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129'); Query OK, 1 row ... Read More

You need to use backticks around table name as well as database name. The syntax is as follows:UPDATE `yourDatabaseName`.`yourTableName` SET yourColumnName1=yourColumnName1+1 WHERE yourColumnName2=’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> use test; Database changed mysql> create table Add1Demo    -> (    -> Id varchar(10),    -> Value int    -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add1Demo values('1', 780); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1Demo values('2', ... Read More

You need to use CONVERT() function along with binary keyword. The syntax is as follows −SELECT CONVERT(binary CONVERT(yourColumnName using latin1) USING UTF8) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UtfDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(15),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into UtfDemo(Name) values('Obama’s'); Query OK, 1 row affected (0.28 ... Read More

You can achieve it with the help of GROUP_CONCAT() function. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..N, GROUP_CONCAT(yourColumnName4) as anyAliasName FROM yourTableName group by yourColumnName3, yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CommaDelimitedList    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> GroupId int,    -> CompanyName varchar(15),    -> RefId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using INSERT command. ... Read More

The binary keyword can be used after WHERE clause to compare a value with exact case sensitive match.The following is an example −Case 1 − Case insensitive matchThe query is as follows −mysql> select 'joHN'='JOHN' as Result;The following is the output −+--------+ | Result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)In the above sample output, the result is true while we know joHN and JOHN are two different words. This is not a case sensitive match.Case 2 − If you want case sensitive match, use the binary keyword.The query is ... Read More

You can get year out of timestamp using YEAR() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE YEAR(yourTimestampColumnName)='yourYearValue’';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table getYearOut    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> yourTimestamp timestamp default current_timestamp,    -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.56 sec)Insert some records in the table using INSERT command−mysql> insert into getYearOut(Name, yourTimestamp) values('John', now()); Query OK, 1 row affected (0.26 sec) ... Read More

The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table castTypeToBigIntDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Value varchar(100),    -> PRIMARY KEY(Id)    -> ); Query OK, ... Read More

To understand the group by with where clause, let us create a table. The query to create a table is as follows −mysql> create table GroupByWithWhereClause    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> IsDeleted tinyint(1),    -> MoneyStatus varchar(20),    -> UserId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) values(0, 'Undone', 101); Query OK, 1 row affected (0.17 sec) mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) ... Read More

You can use a subquery with JOIN condition for this. The syntax is as follows −SELECT yourTablevariableName.* FROM ( SELECT MAX(UNIX_TIMESTAMP(yourDateTimeColumnName)) AS anyAliasName FROM getLatestHour GROUP BY HOUR(UserLoginDateTime) ) yourOuterVariableName JOIN yourTableName yourTablevariableName ON UNIX_TIMESTAMP(yourDateTimeColumnName) = yourOuterVariableName.yourAliasName WHERE DATE(yourDateTimeColumnName) = 'yourDateValue';To understand the above syntax and the result to be achieved, let us create a table. The query to create a table is as follows −mysql> create table getLatestHour -> ( -> UserId int, -> UserName varchar(20), -> UserLoginDateTime ... Read More

You can use IFNULL along with ORDER BY clause. The syntax is as follows −SELECT *FROM yourTableName ORDER BY IFNULL(yourColumnName1, yourColumnName2);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table IfNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ProductName varchar(10),    -> ProductWholePrice float,    -> ProductRetailPrice float,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) values('Product-1', 99.50, ... Read More