Skip to content

ErcanOPAK.com

  • ASP.Net WebForms
  • ASP.Net MVC
  • C#
  • SQL
  • MySQL
  • PHP
  • Devexpress
  • Reportviewer
  • About
SQL

How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()

- 20.07.22 | 21.07.22 - ErcanOPAK

In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well.

SELECT
   <column(s)>
FROM
   <table(s)>
ORDER BY
   <sort column(s)>
OFFSET 1 ROWS   -- Skip this number of rows
FETCH NEXT 1 ROWS ONLY;  -- Return this number of rows

Note that this query template requires “ORDER BY” clause.
If you don’t want to use that clause then you can write it as – Order by (Select NULL)  

Here is another example: Limit with offset to select 11 to 20 rows in SQL Server.

SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
  • ORDER BY: required
  • OFFSET: optional number of skipped rows
  • NEXT: required number of next rows

 

You can use ;with as second options:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

The query result format is in the following example.

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

;with myTableWithRowNumbers AS(select row_number() over(order by salary DESC) as 'row', salary from Employee GROUP BY salary)
select salary as SecondHighestSalary from myTableWithRowNumbers where row = 2

 

 

Related posts:

How to get stored procedure parameters details in SQL
Search text in all tables in SQL Server
Add Constraint to SQL Table to ensure email contains @
How to get triggers create and update date in SQL
Return number of rows affected by UPDATE statements in SQL
Post Views: 8

Post navigation

How to change the style of scrollbar with CSS
How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

August 2022
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
293031  
« Jul    

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (362)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (234)
  • Find numbers with more than two decimal places in SQL (203)
  • Confirm before process with ASPxButton in Devexpress (199)
  • Add Constraint to SQL Table to ensure email contains @ (199)
  • ASPxGridView – Disable CheckBox based on condition in GridViewCommandColumn (198)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (197)
  • How to make some specific word(s) Bold or Underline in ReportViewer (191)
  • Devexpress ASPxGridview Column Grouping in Code (183)
  • Tense Changes When Using Reported Speech (171)

Recent Posts

  • How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid
  • How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()
  • How to change the style of scrollbar with CSS
  • How to add scroll bar to div in Asp.Net
  • How to solve “Fatal: Not possible to fast-forward, aborting” problem
  • How to add Output Parameter to SqlDataSource
  • How to get stored procedure parameters details in SQL
  • How to add default value for Entity Framework migrations for DateTime and Bool
  • String Interpolation, String Format, String Concat and String Builder in C#
  • How to get triggers create and update date in SQL

Recent Posts

  • How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid
  • How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()
  • How to change the style of scrollbar with CSS
  • How to add scroll bar to div in Asp.Net
  • How to solve “Fatal: Not possible to fast-forward, aborting” problem

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (362)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (234)
  • Find numbers with more than two decimal places in SQL (203)
  • Confirm before process with ASPxButton in Devexpress (199)
  • Add Constraint to SQL Table to ensure email contains @ (199)

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter

© 2022 ErcanOPAK.com

Proudly powered by WordPress | Theme: Xblog Plus by wpthemespace.com