Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2009

    Unanswered: SELECT second record, with additional WHERE clause

    Hey all!

    I'm trying to select the second row out of a table, with a few additional conditions to keep it interesting .

    I'm aware that the basic principle for selecting the second row out of a table is

    SELECT TOP 1 t2.fieldname 
    FROM (
       SELECT TOP 2 t1.fieldname, t1.orderfield 
       FROM tbl as t1 
       ORDER BY t1.orderfield ASC
       ) AS t2 
    ORDER BY t2.orderfield DESC
    but it is the additional conditions that complicate things.

    The tables
    Let me first describe my tables.

    Table 'c' with contact info.
    For this purpose trimmed down to 2 fields:
    • 'cID': the contact ID
    • 'firstname': name of the contact

    cID firstname
    1 ruud
    2 piet
    3 sjaak
    4 christ

    Table 'p' with phone numbers.
    • 'ID': the ID number uniquely identifying the records
    • 'cID': the ID number of the contact to which the number belongs
    • 'prio': the priority of the phone number (a number between 0 and 10, 10 for the highest-priority phonenumber).
    • 'Phone': the actual phone number

    ID cID prio Phone
    2 1 7 ruud: lowest priority phonenumber
    3 1 10 ruud: highest priority phonenumber
    4 1 8 ruud: third-highest priority phonenumber
    5 1 9 ruud: second-highest priority phonenumber
    6 2 7 piet: only phonenumber
    7 3 8 sjaak: third-highest priority phonenumber
    8 3 9 sjaak: second-highest priority phonenumber
    9 3 10 sjaak: highest priority phonenumber
    10 3 7 sjaak: lowest priority phonenumber

    (sorry for the terrible formatting)

    Good. Now the queries.

    I want to create a query that returns exactly one record per contact. The fields of this query should be
    • the contact's ID number ('cID')
    • the contact's name ('firstname')
    • the phone-record's ID number ('ID')
    • the phone number ('phone').

    I have been able to create a query that returns the HIGHEST-priority phonenumber for each contact:

    SELECT c.cID, c.firstname, p3.ID, p3.Phone
       SELECT * FROM p AS p1 WHERE p1.ID IN (
          SELECT TOP 1 ID FROM p AS p2 WHERE p1.cID = p2.cID ORDER BY p2.Prio DESC
       ) AS p3 ON c.cID=p3.cID;
    which gives

    cID firstname ID Phone
    1 ruud 3 ruud: highest priority phonenumber
    2 piet 6 piet: only phonenumber
    3 sjaak 9 sjaak: highest priority phonenumber
    4 christ (Null) (Null)

    But I'm now trying to adjust this query so that the phone number is the SECOND-HIGHEST priority phonenumber for this person.

    So I want it to return:

    cID firstname ID Phone
    1 ruud 3 ruud: second-highest priority phonenumber
    2 piet (Null) (Null)
    3 sjaak 9 sjaak: second-highest priority phonenumber
    4 christ (Null) (Null)

    I've tried various things to include a second 'SELECT TOP...ORDER BY' subquery, but none seems to work.

    Suggestions, anyone?


    Some remarks:
    • Don't forget that Piet (cID=2) has got only 1 phonenumber, so when asking for the second-highest-priority number none should be returned.
    • I'm using MS Access 2007
    • If of any help I can of course prepare an Access file with this table and these queries.
    • Most importantly: if I'm being completely overly laborious and there is a much easier or faster (in terms of cpu-time) of doing this than please tell me!
    Last edited by SjaakBanaan; 11-01-09 at 14:39. Reason: text formatting

Posting Permissions

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