MySQL

In this article we are going to discuss about how to create temporary table in MySQL.

MySQL has a property to create a temporary table where we can keep keep temporary data.MySQL can also delete this table automatically whenever current session is ended or the user stop the program. WE can also remove it manualy using Drop method.

In MySQL we can create temporary table using ‘Temporary’ keyword.

Create temporary table and insert data

Here we are going to create a temporary table and insert some data in it.

Syntax:

CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM existing_table_name LIMIT 0;

So suppose we have an existing table ‘registration_details’,

Now we are going to create a new table with the help of our existing table ‘registration_details’ below is our query for this,

CREATE TEMPORARY TABLE registered_employee
SELECT first_name, desig_post FROM registration_details WHERE desig_post ="Developer"  AND last_name="Mishra" ;

Now by using below select query we will see our output,
select * from registered_employee;

So you can see that we have got our output registered_employee having only those employee who have last_name =’mishra’ and desig_post=’developer’ as we have given condition.

Now we are going to add some data using insert query,let’s see how it will work

INSERT INTO registered_employee(first_name,desig_post)
SELECT first_name,desig_post
FROM registration_details
WHERE
desig_post="tester"
;

Here is our output:

In above query you can see that we have given condition desig_post=”tester” so it added those name having tester designation.

Create temporary table in a select statement without a separate create table

Now we are going to create a table using ‘AS’ keyword,below is the query for it.

CREATE TEMPORARY TABLE IF NOT EXISTS registered_employee AS 
 (SELECT first_name,desig_post  FROM registration_details 
 WHERE desig_post ="developer" & "tester");

Here is output:

Create a temporary table in MySQL with an index

Now we are going to create a temporary table with an index,

Syntax:

CREATE TEMPORARY TABLE temporary_table_name (index_column_name INTEGER NOT NULL AUTO_INCREMENT, PRIMARY
KEY(index_column_name), INDEX(index_column_name))
SELECT * FROM existing_table_name
WHERE  ;

Now let’s take an example to understand this,

CREATE TEMPORARY TABLE registered_employee_with_index (registration_number INTEGER NOT NULL AUTO_INCREMENT, PRIMARY    
KEY(registration_number), INDEX(registration_number))
SELECT * FROM registration_details
WHERE desig_post ="developer" ;

Here is our output:

So you can see that above we have given condition that ‘WHERE desig_post =”developer”‘ and we got our new table “registered_employee_with_index” with index number.

Add a column to temporary table in MySQL

Here we are going to add a new column using Alter command in our temporary table,

Syntax:

ALTER TABLE temporary_table_name ADD new_column_name  
DEFAULT default_value;

Let’s take an example,

We will be use above table registered_employee_with_index and adding a new column state_name to it.

ALTER TABLE registered_employee_with_index ADD state_name varchar(255) 
DEFAULT 'Andhra Pradesh';

You can see in the above output, a new column state_name got added to the table registered_employee_with_index.

Conclusion:

In this article we have discussed  about how to create temporary table in MySQL using different method.Thank you!

In this article we are going to discuss how to SELECT  first row of MySQL table.

Here we are going to create a table ‘sales_team_emails’ which we will be using in our article,

CREATE TABLE sales_team_emails (
    sales_person_id INT AUTO_INCREMENT,
    sales_person_name VARCHAR(255),
    sales_person_email VARCHAR(255),
    PRIMARY KEY (sales_person_id)
);

Insert values in table;

INSERT INTO sales_team_emails (sales_person_name,sales_person_email) 
 VALUES("Shikha","shikha@managementbliss.com"),
 ("Rahul","Rahul.Abey@me.com"),
 ("Varun","Varun.Loud@me.com"),
 ("Aarav","Aarav@managementbliss.com"), 
 ("Ishan","Ishan@mine.com"),
  ("Aditi","Aditi.Sun@me.com"),
 ("Ram","Ram.Thomas@mine.com"),
 ("Aditi Sharma","aditi@managementbliss.com"),
 ("Etiv","Etiv.Abey@me.com"),
 ("Ishu","Ishu.Freebird@me.com"),
 ("Siya","Siya.com");

Output:

Example 1:

Now we are going to select  first record of a table using below query,

SELECT * FROM sales_team_emails LIMIT 1;

You can see that here we have used LIMIT because we want to select only first row.We use LIMIT whenever we want some restrictions.

Output:

In output you can see that we have got first row value from table ‘sales_team_emails‘.

Example2:

Now we are going to get record of a row which user want like,SELECT first record  the table sales_team_emails where sales_person_name is Ram.

SELECT 
    sales_person_id, sales_person_email
FROM
    sales_team_emails
WHERE
    sales_person_name = 'Ram'
LIMIT 1;

Output:

Here you can see that we have got desired output like this we can get any row value.

Conclusion:

In this article we have discussed how to SELECT  first row of MySQL table.Thank You!

Methods to insert records if not exist in MySQL

In this article, we discuss how to insert single or multiple records in SQL tables if they are not present. If records are already present then no updation occurs.

Before going to the actual content first let create a table and insert some dummy data into it.

CREATE TABLE student_details(
    student_id INT auto_increment,
   student_name VARCHAR(255),
   student_address VARCHAR(255),
    primary key (student_id));
INSERT INTO student_details (student_name,student_address) 
 VALUES("Mayank","Kolkata"),
("Raj","Delhi"),
("Abhay","Delhi"),
("Aman","Jaipur"),    
("Rahul","Mumbai"),
("Shubh","Merrut");
select * from student_details;

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut

So this is our student_details table.

Now we will see how to insert single or multiple records if not exist in the table. Let first see the syntax or sample SQL query to do this task after that we will implement the query on our table.

INSERT NOT EXISTS Syntax

If a subquery returns any rows at all, NOT EXISTS subquery is FALSE. It means that if the subquery in the NOT EXIST clause is TRUE, it will return no rows.

Syntax:INSERT INTO table_name (column1, column2, ....)                                           SELECT * FROM (SELECT value1, value2,....) AS temp                                          WHERE NOT EXISTS (<conditional_subquery>);

table_name: It is the name of that table where we want to insert the data.

<conditional_subquery>: It is the sub-query including a select statement to get the row with a particular condition.

INSERT single record if NOT EXISTS in MySQL

Now with the help of the above query, we will see how to insert a single record if it is NOT EXISTS in MySQL.

INSERT INTO student_details (student_name,student_address) 
SELECT * FROM (SELECT 'Manish' AS student_name, 'Agra' AS student_address) AS temp 
WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Manish' ) LIMIT 1

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut
7	Manish	Agra

Here we see that one record with student_name=’Manish’ and student_address=’Agra’ is inserted.

Suppose we will fire this query again. Let see what happens.

 0 rows inserted. (Query took 0.0012 seconds.)

When we try to execute this query again we see that this time no record is inserted because the record is already present in the SQL table. So we can say that this query only inserts records when NOT EXIST in MySQL.

INSERT multiple records if NOT EXISTS in MySQL

What if we have more than one record to be inserted, and before every insert, we want to ensure that the record with the same column value does not exist. Let see how to write an SQL query for this.

INSERT INTO student_details (student_name, student_address) 
SELECT student_name, student_address FROM 
( SELECT student_name , student_address FROM 
( SELECT 'Ravi' as student_name , 'Shimla' as student_address ) AS temp_1 
WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Ravi' ) 
UNION ALL 
SELECT student_name, student_address FROM ( SELECT 'Raju' as student_name , 'Bandra' as student_address ) AS temp_2 WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Raju' ) ) 
alias_student_details

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut
7	Manish	Agra
8	Ravi	Shimla
9	Raju	Bandra

So here we see another 2 records inserted in the SQL table. So we see how to insert multiple or single records if NOT EXIST in MySQL.

mysql insert select: In this article we are going to discuss how to insert data into a table using select method in MYSQL.

MYSQL INSERT WITH SELECT: SINGLE TABLE

Select method copies data from one table and inserts into another table.

Syntax:

INSERT INTO table_name(column_name)
SELECT tbl_name.col_name
FROM some_table tbl_name
WHERE condition;

Lets take an example,here we will create a table employees_regist ,which will then select some data from an already existing table emplyees_details in our database.

emplyees_details table has columns emloyee_no, first_name, last_name and designation_post. To see all the rows in employees_details table we will do a SELECT * query on it.

SELECT * FROM employee_details;

After this query you will get this result,

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       | Mishra       | Developer |
|                    2 | Ritika         | Mathur      | Designer   |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer |
|                    5 | Aadhya      | Roopam    | Techwriter |
+-------------+------------+-----------+------------+

So you can see that we already have one table(employee_details), now we are going to create our new table ’employee_regist’.

CREATE TABLE employee_regist(
    employee_exp INT AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    employee_post VARCHAR(255),
    employee_salary INT,
    PRIMARY KEY (employee_exp)
);

So you can see that we have created a table but there is no value because our table is empty at this time,if you want to see table you can write select query,

SELECT * FROM employee_regist;

Output:

Empty set (0.05 sec)

Now we are going to add values in employee_regist.For this we are going to write INSERT query.

INSERT INTOemployee_regist(emploee_exp,first_name,last_name ) 
 SELECT employee_no,first_name,last_name FROM employee_details 
 WHERE desig_post = 'Developer' ;

Now to show output we will execute our SELECT query ,

SELECT * FROM registration_details;

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       | Mishra       | Developer |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer ||
+-------------+------------+-----------+------------+

So in above table you have seen that all table not added only those satisfying the given condition were addaed.

MYSQL INSERT WITH SELECT: MULTIPLE TABLES

In above table you have seen that we have added rows and column from single table but you can also do the same with multiple table.Let’s see it by an example,

We have employee_regist,emplyee_details and eregistered_employee already present in our database,

Now we are going to execute SELECT query to see the data in these table one by one,

’employee_regist’

SELECT * FROM employee_regist;

Output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | confirm     |
+-------------+------------+-----------+------------+
|                      |                    |                  |                   |
|                      |                    |                  |                   |
|                      |                    |                  |                   |
+-------------+------------+-----------+------------+

’employee_details’

SELECT * FROM employee_details;

output:

+-------------+------------+-----------+------------+
| employee_no | first_name | last_name | desig_post |
+-------------+------------+-----------+------------+
|                    1 | Shikha       |  Mishra      | Developer |
|                    2 | Ritika         | Mathur      | Designer   |
|                    3 | Rohan        | Rathour     | Developer |
|                    4 | Aarush       | Rathour     | Developer |
|                    5 | Aadhya      | Roopam    | Techwriter |
+-------------+------------+-----------+------------+

’employee_status’

SELECT * FROM registered_employee;

Output:

+-------------+------------+-----------+------------+
|  first_name | last_name | confirm                            |
+-------------+------------+-----------+------------+
|         Shikha | Mishra     | yes                                    |
|           Ritika | Mathur    | yes                                    |
|          Rohan | Rathour   | No                                    |
|          Aarush| Rathour   |yes                                     |
|        Aadhya | Roopam  | No                                     |
+-------------+------------+-----------+------------+

Now we are going to write a query in MySQL to get the data into employee_regist from  employee_details and registered_employee.

Selecting first_name and last_name from employee_details and confirm from employee_registered of only those students who have ‘Developer‘ stream.

INSERT INTO employee_regist(employee_no,first_name,last_name, employee_confirm) 
SELECT em.employee_no,em.first_name,em.last_name ,re.confirm
FROM  employee_details em , confirm re
WHERE em.first_name = re.first_name 
AND em.last_name = re.last_name
AND em.stream_name = 'Developer'  ;

Above in our query we have used em and re as alias,So now we are going to see output It will add data from multiple table.

Output:

+————-+————+———–+————+
| employee_no | first_name | last_name | confirm |
+————-+————+———–+————+
| 1                    | Shikha       | Mishra      |yes          |
| 3                    | Rohan       | Rathour     |No          |
| 4                    | Aarush      | Rathour     |yes          |

+————-+————+———–+————+

Conclusion:

In this article we have discussed how to insert data into a single and multiple table using select method in MYSQL.

Thanks to all!

MySQL delete column: In this article we are going to discuss how to delete or drop a column from a table in MYSQL.

• MySQL Delete Column From a Table – Syntax
• MySQL Delete Column From a Table – example
• MySQL Drop a Column With Foreign Key

Now first we are going to create a table students_data which we will be using in our article to show how to delete or drop a column from a table in MYSQL,

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(50),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);

Insert values in it by using below query,

INSERT INTO students_data (student_name, student_address, student_grade, student_subject) 
VALUES("Dev","23A Delhi","A","Maths"),
("Riya","188 Pune","B","Hindi"),
("Harsh","78 Indore","C","Hindi"),
("Ravi","58 Cochi","A","Physics"),    
("Aarush","18 Mumbai","B","Physics"),
("Ishan","43 Lucknow","C","Geography"),
("Tanya","78 Arouli","D","Geography"),
("Monika","255 Punjab","A","Chemistry"),
("Atharv","587 Chandigarh","A","Chemistry");

Output:

MySQL delete column from a table -syntax

Delete column from table MySQL: MySQL allows  ALTER TABLE command to add a new column to an table and we can also drop/delete column using this. The following are the syntax to do this:

Syntax:

ALTER TABLE table_reference DROP COLUMN column_name;

Explaination:

1.ALTER TABLE: This is Alter Command

2.table_reference – table from which we have to delete column

3.column_name – the name of the column which will get deleted.

MySQL delete column from a table – example

Drop column mysql: Here we are going to take an example to understand Alter Table Command,now we are going to delete column ‘student_subject’ from ‘student_data’ table using below query,

ALTER TABLE students_data DROP COLUMN student_subject;

Output:

You can see that in above output ‘student_subject’ column has deleted.

If we want other sessions to read and write the table while we delete a column we can put LOCK value is none which you can see in below query,

ALTER TABLE students_data DROP COLUMN student_subject, ALGORITHM= INPLACE , LOCK=NONE;

MySQL drop a column with foreign key

MySQL remove column: Here we are going to use another method to drop/delete column in MySQL.Let first create a table where we will also create a foreign key constraint,

Let’s create,

CREATE TABLE students_address_table (
student_address_id INT AUTO_INCREMENT,
student_id INT NOT NULL,
student_address VARCHAR(255),
FOREIGN KEY (student_id) REFERENCES students_data(student_id),
PRIMARY KEY(student_address_id)
);

Insert values in the table “students_address_table”,

INSERT INTO students_address_table (student_id,student_address) 
VALUES(1,"56A Delhi"),
(2,"255 Mumbai"),
(3,"78 Pune"),
(4,"58 Nagpur"),    
(5,"18 Kanpur"),
(6,"43 Rivan"),
(7,"78 Sonbhadra"),
(8,"255 Agra"),
(9,"587 Calcuta");

Output:

In above output you can see that ‘student_address_id’ is primary key and for this ‘student_id’ is foreign key,

Now we are going to write query for drop/delete column in table ‘students_address_table’,

ALTER TABLE students_address_table DROP COLUMN student_id;

Output:

ERROR 1828 (HY000): Cannot drop column 'student_id': needed in a foreign key 
constraint 'students_address_table_ibfk_1'

In above output you can see that we got an error because we have tried to delete column using same query which we have used for delete column using alter command,

Now we are going to make some changes in it first we will delete foreign key constraint,

ALTER TABLE students_address_table DROP FOREIGN key students_address_table_ibfk_1;


ALTER TABLE students_address_table DROP COLUMN student_id;

Output:

 

So you can see that in above output column ‘student_id’ deleted.

Conclusion:

In this article we have discussed how to delete or drop a column from a table in MYSQL.Thank you

MySQL row count: In this article we are going to discuss MySQL select row count or Count() function.

• Count Table Rows
• Select Row Count of a Query
• Select Row Count(distinct)
• Select Row Count GROUP BY
• MySQL Select Row Count From all Tables
• How To Get The Number of Rows That a MySQL Query Returned

Now we are going to create a table ‘customer_sale_details’ which we will be using in our article to show row count() function.

CREATE TABLE customer_sale_details (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    no_products_purchased INT,
    date_of_purchase DATETIME,
    primary key (customer_id)
);

Now we will insert value in it,

INSERT INTO customer_sale_details (customer_name,no_products_purchased,date_of_purchase) 
 VALUES
 ("Hari",12,'2020-11-05 14:29:36'),
 ("David",43,'2020-12-06 15:29:36'), 
 ("Isha",6,'2020-12-12 14:30:36'),
 ("Kiya",8,'2020-10-12 14:39:45'),
 ("Liyo",56,'2021-01-01 13:29:26'),
 ("Jay",13, '2021-01-02 14:40:36'),
 ("Oam",134,'2021-01-02 12:30:30'),
 ("Mounika",183,'2020-10-05 11:19:36'),
 ("Tanya",13,'2019-11-05 12:31:31'),
 ("Arya",13,'2019-11-05 11:39:36'),
 ("Hary",145,'2021-01-07 10:10:16'),
 ("Goyal",45,'2021-01-07 11:11:33');

Output:

Count table rows

Here we will discuss how to count number of rows in MySQL table.Below is the query for it,

SELECT COUNT(*) FROM customer_sale_details ;

Output:

+———-+
| COUNT(*) |
+———-+
| 12 |
+———-+

Here in above output you can see that there is 12 rows in our table.

Select row count of a query

Here we are give a condition based on that it will return row number.See the below query,

SELECT count(*) FROM (SELECT * FROM customer_sale_details WHERE customer_name="George") AS count_of_query;

Output:

+———-+
| count(*) |
+———-+
| 1 |
+———-+

Select row count(distinct)

Here we are going to get distinct customer name from the table.See below query,

SELECT COUNT(DISTINCT customer_name) AS Customer_Names FROM customer_sale_details;

Output:

+—————-+
| Customer_Names |
+—————-+
| 12 |
+—————-+

Let’s take some other condition to show distinct customer_name from the table.

SELECT COUNT(customer_name) FROM (
    SELECT DISTINCT customer_name FROM customer_sale_details  WHERE no_products_purchased  < 100
) AS customer_count;

Output:

———————-+
| COUNT(customer_name) |
+———————-+
| 9 |
+———————-+

Select row count GROUP BY

Here we are going to show working of GROUP BY using COUNT() function in it.Now we will get number of records GROUPED BY customer_name,

SELECT 
    customer_name, COUNT(customer_name) AS records_per_customer
FROM
    customer_sale_details
GROUP BY customer_name

Output:

MySQL select row count from all tables

In this we are going to discuss different ways to get row count of all table present in database schema.

Syntax:

SELECT 
    TABLE_NAME, SUM(TABLE_ROWS) AS COUNT_OF_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'Name of your schema'
GROUP BY TABLE_NAME;

Example:

Here we are going to get the count of all the tables which are present in the schema ‘abc’.

SELECT 
    TABLE_NAME, SUM(TABLE_ROWS) AS COUNT_OF_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'abc'
GROUP BY TABLE_NAME;

Output:

 

You can also see status by using below query;

SHOW TABLE STATUS;

How to get the number of rows that a MySQL query returned

Here we are going to use SELECT FOUND_ROWS() which will return previous query rows.

SELECT * FROM customer_sale_details WHERE no_products_purchased < 50;

Output:

You can see that in output  we have got 8 rows.If you want to see the returned rows from last query you can get by using below query,

SELECT FOUND_ROWS();

Output:

+————–+
| FOUND_ROWS() |
+————–+
| 8 |
+————–+

Conclusion:

In this article we have discussed in deep MySQL select row count or Count() function.Thank You!

MySQL loop example: In this article we will discuss about For Loop in MySQL with examples.

Loops in MySQL

Loop is a order of instructions that is continually replicated until a given condition is reached. MySQL also provides loop functionality like other languages.

Syntax:

label_for_loop: LOOP
IF <condition> THEN
LEAVE label_for_loop;
END IF;
<statements to be executed - separated by ';' >
ITERATE label_for_loop;
END LOOP;

Parameters:

1.labelname:It is an non-mandatory label at the start and end.

2.loop:For start and end loop.

3.statements:Condition which you give for execution

Flow of executions in For Loop:

MySQL For Loop Examples

Let’s understand it by example,

Example 1: Loop through and display the ‘%’ five times

DROP PROCEDURE IF EXISTS for_loop_star;
DELIMITER $$
CREATE procedure for_loop_star()
BEGIN
  DECLARE x INT;
  DECLARE percentage VARCHAR(50);
  DECLARE f_output VARCHAR(50);
  SET x = 1;
  SET star= "%";
  SET f_output ="";
  forloop: LOOP
    IF x > 5 THEN
    LEAVE forloop;
    END IF;
   SET x = x + 1;
   set f_output = CONCAT(f_output, percentage);
  END LOOP;
SELECT f_output;
END $$
DELIMITER ;
CALL for_loop_percentage();

In above code you can see that we have created a strategy for_loop_percentage() to run for loop to print the ‘%’ five times .Initial x value is 1,after every loop it increment by 1.Percentage concatenates with f_output in every iteration,after that checks the value of x, and if it is greater than five the loop will terminate.

Using SELECT f_output statement we will get our output.

Output:

f_output-
%%%%%

Example2:CREATE TABLE Mytable (value VARCHAR(50) NULL DEFAULT NULL);

DELIMITER $$ 
CREATE PROCEDURE ADD()
 BEGIN
  DECLARE a INT Default 1 ;
  simple_loop: LOOP     
    insert into table1 values(a);
    SET a=a+1;
    IF a=11 THEN
      LEAVE simple_loop;
    END IF;
 END LOOP simple_loop;
END $$

Queries to check the output –

CALL ADD();
Select value 
from Mytable;

Output:

1
2
3
4
5
6
7
8
9
10

Example3:

DELIMITER $$
CREATE FUNCTION Mydemo (value1 INT)
RETURNS INT
BEGIN
 DECLARE value2 INT;
 SET value2 = 0;
 label: LOOP
  SET income = value2 + value1 ;
  IF value2 < 4000 THEN
    ITERATE label;
  END IF;
  LEAVE label;
 END LOOP label;
 RETURN value2 ;
END $$
DELIMITER ;

Queries to check the output :

CALL Mydemo();

Input –

value1: 3500

Output –

value2: 3500

Conclusion:

In this article we have discussed about For Loop in MySQL with different examples.Thank you!

Mysql select first row: In this article we are going to discuss how to get first record or select first row in each group in MySQL.

• Select First Row in Each Group Using Min() Function
• Select First Row in Each Group Using Join
• Select First Row in Each Group Using Not Exists

Let’s assume we have a table sales_details(sale_person_name,no_products_sold,sales_department) that contains sales data for multiple products.

# create the table sale_details
CREATE TABLE sale_details (
    id INT auto_increment,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    primary key (id)
);
#insert rows into sale_details
INSERT INTO sale_details (sale_person_name,no_products_sold,sales_department) 
  VALUES
 ("Rekha",1200,"Kichten Essentials"),
 ("Rekha",5000,"Apperals"),
 ("Rekha",59,"Medicines"),
 ("Vivek",1300,"Kichten Essentials"),
 ("Vivek",600,"Apperals"),
 ("Vivek",570,"Medicines"),
 ("Dev",300,"Kichten Essentials"),
 ("Dev",450,"Apperals"),
 ("Varun",900,"Medicines"),
 ("varun",500,"Kichten Essentials"),
 ("Git",1000,"Apperals"),
 ("Git",2000,"Medicines");

Output:

select * from sale_details;

 

Here we will discuss three methods for doing this,

Select First Row in Each Group Using Min() Function

Using min() function we will get minimum value of every group and let us see its usage to get the first row of the each group.

SELECT *
  FROM sale_details 
 WHERE id IN (
               SELECT min(id) 
                 FROM sale_details 
                GROUP BY sale_person_name
             );

In above code you can see that we have used min() function which gave us min value of every column and with help of it we will get first record of each group in table.Here we have used groub_by to get first record of each .Which you can see in our output.

Output:

Select First Row in Each Group Using Join

Here self join is use to get the first record of each group.We have used left join to join table itself.

SELECT
    sd1.*
FROM
    sale_details AS sd1
    LEFT JOIN sale_details AS sd2 ON (
        sd2.sale_person_name = sd1.sale_person_name
        AND
        sd2.id < sd1.id
    )
WHERE
    sd2.sale_person_name IS NULL;

Output:

Select First Row in Each Group Using Not Exists

Here we will use NOT EXISTS to get first row of each group.If subquery returns any rows it means Exits subquery is True and Not Exits subquery is False.

SELECT * FROM sale_details sd1
WHERE NOT EXISTS (
   SELECT 1 FROM sale_details sd2 
   WHERE sd2.sale_person_name = sd1.sale_person_name 
   AND sd2.ID < sd1.ID 
);

Output:

Conclusion:
In this article we have discussed how to get first record or select first row in each group using different methods in MySQL.Thank You