For this, you can use ORDER BY CAST(). Let us see an example −mysql> create table DemoTable2006 (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserCode    varchar(20) ); Query OK, 0 rows affected (1.14 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2006(UserCode) values('John_12'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable2006(UserCode) values('John_34'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2006(UserCode) values('John_56'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_101'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_103'); Query ... Read More

To get minimum value from a column with corresponding duplicate ids, use GROUP BY and MIN() −select min(yourColumnName) from yourTableName group by yourColumnName;To understand the above syntax, let us create a table −mysql> create table DemoTable2005 (    Id int,    Price float ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2005 values(1, 56.88); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2005 values(1, 120.56); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select * from DemoTable2005;This will produce ... Read More

Let us first create a table −mysql> create table DemoTable2004 (    UserId varchar(20),    UserName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2004 values('John_123', 'John'); Query OK, 1 row affected (0.93 sec) mysql> insert into DemoTable2004 values('23456_Carol', 'Carol'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable2004 values('111_Bob', 'Bob'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable2004;This will produce the following output −+-------------+----------+ | UserId      | UserName | +-------------+----------+ | ... Read More

To display highest amount from corresponding duplicate ids, use MAX() along with GROUP BY clause −mysql> create table DemoTable2003 (    CustomerId int,    Amount int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2003 values(101, 560); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2003 values(102, 1080); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable2003 values(101, 570); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2003 values(102, 870); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable2003 values(101, 460); ... Read More

To group dates in MySQL, use the GROUP BY clause −mysql> create table DemoTable2002 (    CustomerName varchar(20),    CustomerShippingDate datetime ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2002 values('Chris', '2019-01-10'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable2002 values('David', '2018-12-31'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2002 values('David', '2019-12-16'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2002 values('Chris', '2018-12-01'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * ... Read More

To get the maximum exam date with a user-defined variable, the code is as follows −select date(max(yourColumnName )) into @yourVariableName  from yourTableName;To understand the above syntax, let us first create a table −mysql> create table DemoTable2001 (    ExamDate date ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2001 values('2019-01-10'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2001 values('2018-12-31'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2001 values('2018-11-18'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable2001 values('2019-07-25'); Query OK, 1 ... Read More

To display right single quotation marks, you need to alter the table with COLLATE='utf8_unicode_ci'.Let us first create a table −mysql> create table DemoTable2000 (    Name varchar(20) ); Query OK, 0 rows affected (0.81 sec)Here is the query to use collate −mysql> ALTER TABLE DemoTable2000 COLLATE='utf8_unicode_ci'; Query OK, 0 rows affected (0.90 sec) Records: 0  Duplicates: 0  Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable2000 values('Chris’s Brown'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable2000 values('David’s Miller'); Query OK, 1 row affected (0.67 sec) mysql> insert into DemoTable2000 values('Robert’s Downey'); Query ... Read More

To return only the month name, you can use DATE_FORMAT() −mysql> create table DemoTable1999 (    ArrivalDate timestamp ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1999 values('2019-01-01 12:34:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1999 values('2019-12-31 10:04:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1999 values('2018-10-11 04:04:30'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1999;This will produce the following output −+---------------------+ | ArrivalDate         | +---------------------+ ... Read More

To return only a single row from duplicate rows, use DISTINCT keyword −mysql> create table DemoTable1998 (    Name varchar(20) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1998 values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1998 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1998 values('Robert'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable1998 values('David'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1998 values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1998 ... Read More

To convert date to UNIX timestamp, use UNIX_TIMESTAMP() in MySQL −mysql> create table DemoTable1997 (    DueDate date ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1997 values('2018-10-11'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1997 values('2019-12-21'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1997 values('2017-01-31'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select * from DemoTable1997;This will produce the following output −+------------+ | DueDate    | +------------+ | 2018-10-11 | | 2019-12-21 | ... Read More