dense rank in SQL
Posted inMY SQL Oracle PL/SQL SQL

DENSE_RANK SQL

Problem We can easily use the MAX() function in SQL Server to find the maximum value in a table. However, there are situations when the second-highest or third-highest record is needed from the table. SQL Server has no direct SQL functions to fetch the second or third highest from the table, so…
SQL rank function
Posted inSQL MY SQL Oracle PL/SQL

RANK() Function in SQL

The RANK() function in SQL is a powerful window function in SQL Server used to assign a rank to each row within a result set. It is particularly useful when we need to assign a rank to a group of rows based on some sorting criteria and want to differentiate between rows…
SQL LEAD()
Posted inMY SQL Oracle PL/SQL SQL

SQL LEAD()

The LEAD() window function takes a column and an integer offset as arguments and returns the value of the cell in that column that is the specified number of rows after the current row. A third argument can be added to fill cells that do not have a corresponding row. Syntax LEAD(column1_name,…
sql lag()
Posted inOracle PL/SQL SQL SQL Server

SQL LAG()

The LAG() window function facilitates access to previous rows based on the offset argument. It can be particularly useful when a comparison of a previous value is necessary without the use of a self join. There is a similarity to the LEAD() function with the difference being the accessible rows.LEAD() accesses subsequent rows while SQL LAG() accesses…
How to Use ROW_NUMBER OVER() in SQL
Posted inSQL MY SQL Oracle PL/SQL

How to Use ROW_NUMBER OVER() in SQL

Sometimes you need to know the position of rows in a result set. Learn how using ROW_NUMBER and OVER can make it happen! Have you ever needed to add a sequential number to the records returned by an SQL query? Or perhaps you need to create a ‘top n’ report based…
sql subquery
Posted inOracle PL/SQL MY SQL SQL

SQL Subquery

Summary: In this tutorial, you’ll learn how to use SQL subqueries to form flexible queries for retrieving data from the database. Introduction to SQL subquery  A subquery is an SQL query nested inside another query. The query that contains a subquery is known as an outer query. To write a subquery,…
left join in sql
Posted inMY SQL Oracle PL/SQL SQL

SQL LEFT JOIN

Summary: in this tutorial, you’ll learn how to use the SQL LEFT JOIN clause to merge rows from two tables. Introduction to SQL LEFT JOIN clause  The LEFT JOIN clause is an optional clause of the SELECT statement. The LEFT JOIN clause allows you to merge rows from two tables. Here’s the syntax of LEFT JOIN clause: SELECT column1, column2…
Posted inSQL Oracle PL/SQL SQL Server

SQL Server autoincrement

Autoincrement, also known as an identity column or auto-incrementing field, is a feature in SQL Server and many other relational database management systems (RDBMS) that simplifies the process of generating unique, sequential values for a column in a table. This feature is commonly used for primary keys, ensuring that each…
Posted inMY SQL Oracle PL/SQL SQL

SQL remove duplicates

Removing duplicates from a SQL database is a common task that can help improve data quality and query performance. Duplicates occur when there are multiple rows in a table with identical values in one or more columns. You can eliminate these duplicates using various SQL techniques and clauses. In this…
Posted inSQL MY SQL Oracle PL/SQL

SQL MERGE

The SQL MERGE statement stands out as a versatile tool for consolidating data from multiple sources and maintaining data integrity. It serves as a powerful alternative to the more traditional approach of using individual INSERT, UPDATE, and DELETE statements to manage data changes. The MERGE statement’s ability to handle these operations within a single statement offers…