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

    Question Unanswered: Multiple Nested (TOP 1 SELECT) s

    Hi guys,

    I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!

    I have the following query:

    SELECT Field1, Field2
    FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
    WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1)
    AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)

    Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):

    SELECT *
    FROM Table1 A
    INNER JOIN Table2 B ON (A.ID=B.ID)
    INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID)
    WHERE
    B.Field1 = C.Field1
    B.Field2 = C.Field1

    Any ideas?

    Many thanks in advance,

    TNT

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    I haven't tested this, but I think it should do what you're looking for...
    Code:
    SELECT Table2.Field1, Table2.Field2 
    FROM Table1
      INNER JOIN Table2 ON Table1.ID = Table2.ID
      INNER JOIN (
        SELECT TOP 1 Field1, Field2
        FROM Table2
        WHERE Field3='X'
        ORDER BY Date1
      ) a ON Table2.Field1 = a.Field1
        AND Table2.Field2 = a.Field2
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Mar 2004
    Posts
    8

    Talking

    Quote Originally Posted by wayneph
    I haven't tested this, but I think it should do what you're looking for...
    Code:
    SELECT Table2.Field1, Table2.Field2 
    FROM Table1
      INNER JOIN Table2 ON Table1.ID = Table2.ID
      INNER JOIN (
        SELECT TOP 1 Field1, Field2
        FROM Table2
        WHERE Field3='X'
        ORDER BY Date1
      ) a ON Table2.Field1 = a.Field1
        AND Table2.Field2 = a.Field2
    Thanks for the response wayneph.
    SQL you posted wouldnt work since in the second JOIN selects only one record, therefore only one record will match.


    i have found another way (still working on it )

    SELECT *
    FROM Table1 A
    INNER JOIN Table2 B ON (A.ID=B.ID)
    INNER JOIN ( SELECT min(Date1), max(Date2) FROM Table2 WHERE Field3='X' ) C ON (A.ID=C.ID)
    WHERE
    B.Date1= C.Date1
    B.Date2= C.Date2

    Some tweaking still needed ..

    Many thanks,
    TNT

Posting Permissions

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