I have written sql query
Select * from Products WHERE Products.Category=="Fruits";
the returned answer is
1-Banana
2-Mango
3-Apple
4-Oranges
5-Grapes
now i want to select fruit at index 3rd only that is Apple using sql indexes the problem arises every where how should i select the element at 3rd place using its index in sql query?
Sql server 2012 supports offset and fetch, so your query should look like this:
SELECT *
FROM Products
WHERE Products.Category ='Fruits'
ORDER BY Products.Category -- or whatever column you need the sort on
OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY;
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY ?
You can use CTE for finding nth place record
with cte as
(
select ROW_NUMBER() over (order by Salary desc) as r, *
from Products
WHERE Products.Category=="Fruits" e
)
select * from cte where r=3
