Sql query to select 3rd element from data?

Question

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?

Syntax error. Which dbms product are you using? Both MySQL and SQL Server??? — jarlh, Jun 04 '15 at 08:34
SQL SERVER 2012 but i want only 3rd number limit shall give me 2 values — Nouman Arshad, Jun 04 '15 at 08:40
Welcome to stackoverflow. Please read [ask]. I've edited your question to remove irrelevant tags. — Zohar Peled, Jun 04 '15 at 08:46

Zohar Peled · Accepted Answer · 2015-06-04T11:07:31.783

2

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;

edited Jun 04 '15 at 11:07

answered Jun 04 '15 at 08:45

Zohar Peled

79,642
10
69
121

May be `OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY` ? – Giorgi Nakeuri Jun 04 '15 at 09:18
Oops, correct. fixed it. – Zohar Peled Jun 04 '15 at 11:07

score 1 · Answer 2 · edited Dec 03 '21 at 14:24

1

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

edited Dec 03 '21 at 14:24

Ergest Basha

7,870
4
8
28

answered Jun 04 '15 at 09:16

Neeraj Singh Chouhan

656
7
13

Sql query to select 3rd element from data?

2 Answers2