SQL Server Window Functions ROWS vs. RANGE

Last Updated : 25 Jan, 2024

We will cover the important concept of the MS SQL Server which is the difference between Row and Range. The confusion between the row and range and where it is used will be eradicated by reading this article. So, be with the flow of the article for a better understanding and keep practicing side by side. Understanding this topic will help you in better understanding of queries in SQL.

So, ROW is a series of persons or things arranged in a usually straight line while RANGE is a variety of things that belong to the same group. The same concept is with SQL Server. The whole concept depends upon these two basic definitions of ROW and RANGE.

Prerequisites

Basics of SQL Server: It will be wonderful to start with this article if you have prior knowledge of the Basics of SQL i.e Creating Database, Creating Tables, and Inserting Values.
You need to be with the flow of an article and practice side by side for a better understanding of the topic.

How ROWS Works

Think of ROW as a way of counting individual rows in your result set. It gives you a single record as a row from the table. Let's understand with the help of the example if we want a record of a single data then the row is used. Helps to assess or manage the data of the single entity that is the row.

Importance: ROW is handy when you want to work with neighboring rows, like calculating the difference between the current row and the one before or after.

How RANGE Works

RANGE is a bit different. Instead of looking at individual rows, it groups rows based on their values. It will give you the set or group of the rows satisfying the condition given by the user. Let's Understand with an example that we want the data of the group of the students having age greater than 15 from th e class. There will be multiple records in the output. Helps to asses the data of the group with some condition satisfying.

Importance:-RANGE is useful when you're more interested in the values themselves rather than the specific rows. It's great for tasks like identifying trends or patterns in your data.

Difference Between ROWS and RANGE

Imagine your data as a bunch of neatly stacked boxes. Rows are like individual boxes, each holding specific information, while range is like a set of boxes grouped together based on certain criteria. Let's unravel this mystery using a virtual table view.

Aspects	Row	Range
Definition	Represents individual records.	Represents a subset based on criteria.
Query Example	SELECT * FROM Table WHERE ID = 1;	SELECT * FROM Table WHERE Column BETWEEN 10 AND 20;
Purpose	Fetches specific individual data.	Retrieves a set of data meeting criteria.
Analogous Scenario	Retrieving a specific box from a stack.	Grouping boxes with a specific label.
Query Syntax	SELECT * FROM Table WHERE condition;	SELECT * FROM Table WHERE Column BETWEEN value1 AND value2;
Result	Returns a single record.	Returns multiple records.

This tabular breakdown provides a quick reference to understand the key distinctions between rows and range in SQL Server. Rows focus on individual records, while range deals with subsets based on specific criteria. Choose rows when you want specific details, and go for range when you need to group data within a defined range.

Example of ROW and RANGE

Lets dive into an example in which we need to display the record of the Information table according to the conditions. By this example you will understand the concept of Range and row more easily.

So start with creating a table for the dummy data so that we can understand concept more easily.

Created a table Information in sql server. You can take a reference from the below given creation and insertion table.

QUERY

--Creation of Table Information
CREATE TABLE Information(
EmployeeID INT,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);
--Inserting the values in the table
INSERT INTO Information VALUES(1, 'John Doe' , 50000.00); 
INSERT INTO Information VALUES(2, 'Jane Doe' , 60000.00); 
INSERT INTO Information VALUES(3, 'Bob Smith' , 75000.00); 
INSERT INTO Information VALUES(4, 'Ankit' , 85000.00); 
INSERT INTO Information VALUES(5, 'Ankush' , 77000.00);

This will create the table Information and will insert the dummy data into it.

Output:

output

Here is the Sample Table to Refer.

Example 1: Using Row

To display one single record of the table we use the WHERE clause. This will Display the Record of the 'Jane Doe' and display all the columns of it.

Query:

SELECT * FROM Information WHERE EmployeeID = 2;

Output:

Explanation:

Here the only that record will be displayed with employee id=2 which is the row of the table Information. Hence the single record is displayed according to the given condition.

Example 2: Using Range

Similarly here also we use where clause with range to display set of records. Here the Output would be all Employee Details whose Salary Comes in between the range form 50000 to 70000. hence the John Doe, Jane Doe, Bob Smith list will be displayed.

Query:

SELECT * FROM Information WHERE Salary BETWEEN 50000.00 AND 75000.00;

Output:

Explanation:

The range based condition shows the multiple rows or records that satisfy the conditions. Hence giving more records in the output.

Conclusion

And there you have it – the difference between rows and range in SQL Server, explained in the simplest terms possible. Rows are your go-to for individual details, while range is best tool for grouping data based on certain conditions. This guide is provided with great detail and is provided in user friendly language. So at last both row and range are the best tools for managing the data it's depend upon the usage and the need where to use that varies.

Window Functions in PL/SQL

sharmaankit

Improve

Article Tags :