Sql Query to retrive Nth record?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • vinaykumar Maladkar
    New Member
    • Feb 2007
    • 17

    #1

    Sql Query to retrive Nth record?

    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
    New Member
    • Jun 2007
    • 43

    #2
    Originally posted by 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.
    HI,
    i am not clear with your requirement.thi s 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 )

    Comment

    • devikacs
      New Member
      • Jun 2007
      • 96

      #3
      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

      Comment

      • Motoma
        Recognized Expert Specialist
        • Jan 2007
        • 3236

        #4
        Originally posted by 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.
        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.

        Comment

        • Infide
          New Member
          • Jul 2007
          • 28

          #5
          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.

          Comment

          • contactmhatre
            New Member
            • Mar 2010
            • 1

            #6
            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.

            Comment

            • Aman Dogra

              #7
              In MySql, To get the nth element:

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

              Comment

              Working...