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:
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.
SQL Server Window Functions ROWS vs. RANGE
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.