Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles by AmitDiwan
Page 801 of 840
MySQL query to order by current day and month?
For this, you can use the ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable ( DueDate date ); Query OK, 0 rows affected (0.56 sec)Note − Let’s say the current date is 2019-07-22.Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-03-10'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-08-25'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-06-01'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2019-12-31'); Query OK, 1 row affected (0.17 sec)Display all records from the ...
Read MoreFix a row value and then ORDER BY DESC rest of the values in MySQL
Let us first create a table −mysql> create table DemoTable ( id int ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable values(7); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(8); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(6); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(9); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(11); Query OK, 1 row affected ...
Read MoreHow to remove unconvertable characters to ASCII with SELECT in MySQL?
Let us first create a table −mysql> create table DemoTable ( Value varchar(100) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('€986'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('§97'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+ | Value | +--------+ | €986 | | §97 +--------+ 2 rows in set (0.00 sec)Following is the query to remove unconvertable characters to ASCII ...
Read MoreWrite a single MySQL query to return the ID from the corresponding row which is a NOT NULL value
Let us first create a table −mysql> create table DemoTable ( StudentId int, StudentName varchar(100) ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL, 'Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(NULL, 'Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(101, 'David'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(102, 'Mike'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(103, 'Sam'); Query OK, 1 row affected (0.18 sec)Display all records ...
Read MoreGet the minimum and maximum value from a VARCHAR column and display the result in separate MySQL columns?
Let us first create a table −mysql> create table DemoTable ( Value varchar(100) ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('190'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('230'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('120'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('189'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------+ | Value | +-------+ ...
Read MorePerform MySQL SELECT on dates inserted into the table as VARCHAR values
Let us first create a table −mysql> create table DemoTable ( DueDate varchar(100) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('21/10/2018'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('18/08/2019'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('01/12/2012'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('31/01/2016'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+------------+ | DueDate ...
Read MoreCopy column values from one table into another matching IDs in MySQL
Let us first create a table −mysql> create table DemoTable1 ( PersonId int, Value int ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(100, 78); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1 values(101, 67); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values(102, 89); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+----------+-------+ | PersonId | Value | +----------+-------+ | 100 ...
Read MoreMySQL CONCAT a specific column value with the corresponding record
Let us first create a table −mysql> create table DemoTable ( FirstName varchar(100), CountryName varchar(100) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Adam', 'US'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('David', 'AUS'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Chris', 'UK'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('John', 'US'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('Carol', 'AUS'); Query OK, 1 row affected (0.14 sec)Display all records ...
Read MoreHow to find a specific record from a list of values with semicolon in MySQL?
For this, you can use FIND_IN_SET(). Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value varchar(100) ); Query OK, 0 rows affected (2.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values('100;200;300'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable(Value) values('1;300;400'); Query OK, 1 row affected (0.58 sec) mysql> insert into DemoTable(Value) values('6;7;8;9;10'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable(Value) values('1;2;3;4;5'); Query OK, 1 row affected (9.36 sec) mysql> insert into DemoTable(Value) values('3;8;9;10'); Query OK, 1 ...
Read MoreMySQL query to order timestamp in descending order but place the timestamp 0000-00-00 00:00:00 first?
Let us first create a table &mnus;mysql> create table DemoTable ( `timestamp` timestamp ); Query OK, 0 rows affected (1.12 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(now()); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('00:00:00'); Query OK, 1 row affected (0.73 sec) mysql> insert into DemoTable values('2018-01-10 12:34:45'); Query OK, 1 row affected (0.80 sec) mysql> insert into DemoTable values('2019-12-31 10:50:45'); Query OK, 1 row affected (0.84 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------------+ | timestamp ...
Read More