Tag: MySQL
Mysql: select rows with MAX(Column value), DISTINCT by another column
We have a requirement to select the rows with maximum value in one column, DISTINCT, by another column in a table. In this article, we will be looking into the same using : ROW_NUMBER() Function and PARTITION BY INNER JOIN and LIMIT Let us start by looking into the data. Assume that we have a …
Mysql: select rows with MAX(Column value), DISTINCT by another columnRead More
Reset AUTO INCREMENT in MySQL
AUTO_INCREMENT is a useful feature provided in MySQL. It works as follows: it generates a numerical sequence to a column for every new row inserted.AUTO_INCREMENT generates unique numbers for identification purposes.We can manually define the starting of this sequence set by auto_increment and reset this value to some other value later. Whenever we reset the …
Reset AUTO_INCREMENT after Delete in MySQL
Sometimes the most recent entries get deleted from our table. After the rows are deleted, try to insert some rows. We can observe a gap in the number_values of the auto_increment column, which is ok in many cases. However, many times the requirement is to reset the auto_increment to maintain the sequence. Well, there is …
Insert into a MySQL table or update if exists
While inserting rows into a table, there are times when the requirement is to insert the rows into a table, but if the key already exists, then update the value. This concept is also known as UPSERT, which is combining UPDATE with INSERT. In this article, we will be looking into : Insert or Update …
MySQL check if table exists
At times there is an essential requirement to find out if a table exists or not in a database, and the same, we will be looking into this article particularly: MySQL check if table exists : SHOW TABLES. MySQL check if table exists : Information Schema. Function to check if the table exists or not …
Find duplicate rows in MySQL
This article will look into how to find duplicate rows in MySQL based on one column and multiple columns. MySQL find duplicate rows based on one column : Using GROUP BY MySQL find duplicate rows based on one column : Using JOINS Find duplicate rows in MySQL with multiple columns : Using GROUP BY Find …
MySQL Delete Duplicate Rows but keep one
In this article, we will see how to delete duplicate rows from a table and keep only one using various ways, mainly: Delete the duplicate rows but keep latest : using GROUP BY and MAX Delete the duplicate rows but keep latest : using JOINS Delete the duplicate row but keep oldest : using JOINS …
MySQL IF EXISTS DROP Table
This article will be looking into how to drop a table if it exists in the database. How to DROP Table IF EXISTS in MySQL How to DROP Temporary Table IF EXISTS in MySQL How to DROP Table IF EXISTS in MySQL What all does a DROP TABLE statement does? DROP TABLE statement will remove one …
Create Temporary Table in MySQL
We can create a temporary table in MySQL using the TEMPORARY keyword. A temporary table is a table that is visible only during the current session and hence has its scope in a single session only. Once the session is closed, access to the temporary table is closed as well. Two different sessions can use …
MySQL select row with max date per user [Solved]
This article talks about selecting a row with a maximum date per user through different methods. Select row with max date per user using JOINS Select row with max date per user using MAX() function Let us get started by creating a table and inserting data into it, which we will be using across this …