How to retrieve the 2nd record from a table ?

How to retrieve the 2nd record from a table which has say 1000 records in it. Please answer this without using the row-id? I tried to say a query "select * from table where rowcount=1" which worked well and also "select * from table where rowcount=3" which gave me the result but not when I did the same query "select * from table where rowcount=2" ? why?

Questions by anabarai   answers by anabarai

Showing Answers 1 - 17 of 17 Answers

Tapas Ranjan gaan

  • Jul 15th, 2011
 

Code
  1. SELECT * FROM table_name WHERE rownum<=2 minus SELECT * FROM poc_account WHERE rownum<=1

    2. 

  2.  

or
Code
  1. SELECT * FROM table_name WHERE rownum<=2 minus SELECT * FROM poc_account WHERE rownum=1

  Was this answer useful?  Yes

pallis

  • Jul 28th, 2011
 

Code
  1. SELECT * FROM ( SELECT <col>,rownum rn FROM <<table>>) WHERE rn=2

    2. 

  2.  

or
Code
  1. SELECT <col> FROM <table> WHERE rownum<3

    2. 

  2. minus

    3. 

  3. SELECT <col> FROM <table> WHERE rownum<2

    4. 

  4.  

NOTE: Columns must be SAME DATA TYPE

  Was this answer useful?  Yes

sivanagireddy

  • Aug 6th, 2011
 

Code
  1.  SELECT * FROM emp WHERE ROWNUM <=2

    2. 

  2.  MINUS

    3. 

  3.  SELECT * FROM emp WHERE rownum<=1

  Was this answer useful?  Yes

Baurisetty Dhiraj

  • Aug 28th, 2011
 

select * from (select * from (select * from table_name where rownum<=2) order by rownum desc) where rownum<=1

  Was this answer useful?  Yes

smonroy

  • Sep 27th, 2011
 

the best way to issue is

select top 1 * from cars where id_car in
(select top 2 id_car from cars order by id_car)
order by id_car DESC

and with that
obtain the second record (This way is for the PK)

  Was this answer useful?  Yes

rima

  • Nov 24th, 2011
 

Code
  1. SELECT * FROM `table_name` WHERE rownum <> 1 ORDER BY rownum  ASC LIMIT 1;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions