Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: how to get 2nd record in the records returned?

    dear all,
    i want to get the 2nd record from a query.
    for example, my query like this.
    "select fullname from officer"
    fullname
    ======
    jim
    mary
    susan
    joe
    parry


    but, i just want to get 2nd record..
    i know "select top..." but it included "jim" as well... u want Mid of the record...
    any help?

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    zecodela, general mistake of all beginers : missing of Primary Key
    MS Access alows you to have table without PK but it can bring lot of problems in the future.
    So I suggest you to have a PK in your Table, in this case you need it to figure out what does it mean - "second row"
    DB isn't excell , where rows are put in some order, in DB query you can get rows in unexpectable order if you don't rder it by yourself by some field(s).

    Now lets suppose you have a PK in your table - ID and seond row means second by ID order
    in this cae you can use this way

    Code:
    SELECT TOP 1 Q1.fullname
    FROM [SELECT TOP 2 ID, fullname
    FROM officer
    ORDER BY ID;
    ]. Q1
    ORDER BY ID DESC;
    Last edited by Kakha; 02-29-04 at 07:57.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    yes, i got it...
    i know what u mean...
    actually, what i want to do is sth like this in oracle or mysql...

    "select fullname from officer limit 2,3"
    or
    "select fullname from officer where rownum = 2"

    but, since msaccess dont support these and need to use subquery as in your code...

    thanks!


    Originally posted by Kakha
    zecodela, general mistake of all beginers : missing of Primary Key
    MS Access alows you to have table without PK but it can bring lot of problems in the future.
    So I suggest you to have a PK in your Table, in this case you need it to figure out what does it mean - "second row"
    DB isn't excell , where rows are put in some order, in DB query you can get rows in unexpectable order if you don't rder it by yourself by some field(s).

    Now lets suppose you have a PK in your table - ID and seond row means second by ID order
    in this cae you can use this way

    Code:
    SELECT TOP 1 Q1.fullname
    FROM [SELECT TOP 2 ID, fullname
    FROM officer
    ORDER BY ID;
    ]. Q1
    ORDER BY ID DESC;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •