Sql Query to retrive Nth record?

[vinaykumar Maladkar]

Hi friends,

I m using MySql and i want to retrive N th recird from a table.
What is the query for that?
Plz help.

Thanx in advance.

[srinit]

Hi friends,

I m using MySql and i want to retrive N th recird from a table.
What is the query for that?
Plz help.

Thanx in advance.

HI,
i am not clear with your requirement.this idea may help you.
You can select top nth record by selecting top(1) record based on your any one column in decreasing order. (or) select top(1) * from yourtable where row1 not in(select top(n-1)row1 from your table )

[devikacs]

I dont know any query that directly gives you the Nth row. It you have a primary key, or a unique key filed, you can
select top n * from table where col not in (select top n-1 * from table)

Another method could be to select top n rows and order them in desc order and select top row from that
or
Select top n rows into a temporary table and select the last row from that table. this would work, even if your table does not have unique entries

[Motoma]

Hi friends,

I m using MySql and i want to retrive N th recird from a table.
What is the query for that?
Plz help.

Thanx in advance.

It seems you have posted in the wrong Forum, or you mislabeled your database as MySQL when you meant MSSQL.

If you are using MySQL, you can use a query like this:
SELECT * FROM table LIMIT 10,1
This will give you the 10th element in the query.

Perhaps, you could explain what you are trying to do, as typically this type of this isn't very useful.

[Infide]

In sql server you could perform the following trick.

SELECT TOP 1 Field1,Field2
FROM (
SELECT TOP 2 Field1,Field2
FROM table1
ORDER BY Field1 asc
) as data
order by Field1 desc

This will give you the 2nd record and only the 2nd record from table1.
Replace the 2 with in the sub select with any number.

[contactmhatre]

Hi Vinay,

You can write the following query if you want to extract 2nd /3rd /4th record from the sql query ....

Say for example you are having 2 tables .....
1. EmpMaster (contains list of all employees)
2. EmpDept (contains information about employee department)

Now say for instance you need to fetch 2th record then you can use the following query .....

select top 1 qry.empid, qry.empfname, qry.emplname, qry.deptid, qry.deptname from
(
select top 2 em.empid, em.empfname,
em.emplname, ed.deptid, ed.deptname
from empmaster em, empdept ed
where em.empid = ed.empid order by ed.deptid ASC
) as qry
order by qry.deptid DESC

First the Inner query will be executed which will arrange the records in ASC order, then the control will go to outer query where the resultset will be reversed in DESC order. So this is how you retrieve the Nth record. If you need to fetch 3rd/4th/5th record then simply make the change in inner query TOP clause.

Cheers,
AMZY.

[Aman Dogra]

In MySql, To get the nth element:

SELECT * FROM <table_name>
where <condition>
limit <n-1>,1