Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: How to select one record per value in inner join

    Hi, any help would be greatly appreciated. I currently have two tables which I am joining. Both of these tables will be joined based on a date/time field.

    Table 1 = PreTemp(Date_Time, Temperature, Pressure)
    Table 2 = Spd (Date_TimeSpd, Speed).

    The purpose of the join is to select records which match and records which are 5 seconds previous to PreTemp's record date_time e.g. 18:34:17 is in PreTemp so 18:34:15 will be selected.

    My current sql code is:

    <code>
    SELECT *
    FROM PreTemp AS p1
    INNER JOIN (SELECT * FROM Spd ORDER BY Spd.Date_TimeSpd DESC) AS s1
    ON (p1.Date_Time=s1.Date_TimeSpd) Or (p1.Date_Time>=s1.Date_TimeSpd
    And s1.Date_TimeSpd>=(p1.Date_Time-(5/86400)));
    </code>

    This does the job to some extent but it selects more than one record in the last 5 seconds, what I need is to select the most up-to-date record in the last 5 seconds from the Spd table based on the date_time field in the PreTemp table.

    Please can you help me, thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I wasn't sure whether you wanted to select the top entry from the PreTemp, or the Top Entry from Spd so I've given both. All you need to do is change the SELECT statement, to SELECT TOP #

    Code:
    SELECT TOP 1 *
    FROM PreTemp AS p1 
    INNER JOIN (SELECT * FROM Spd ORDER BY Spd.Date_TimeSpd DESC) AS s1
    ON (p1.Date_Time=s1.Date_TimeSpd) Or (p1.Date_Time>=s1.Date_TimeSpd
    And s1.Date_TimeSpd>=(p1.Date_Time-(5/86400)));
    or

    Code:
    SELECT *
    FROM PreTemp AS p1 
    INNER JOIN (SELECT TOP 1 * FROM Spd ORDER BY Spd.Date_TimeSpd DESC) AS s1
    ON (p1.Date_Time=s1.Date_TimeSpd) Or (p1.Date_Time>=s1.Date_TimeSpd
    And s1.Date_TimeSpd>=(p1.Date_Time-(5/86400)));

  3. #3
    Join Date
    Jun 2011
    Posts
    3

    Re:

    Thanks for your reply. I still have an issue though e.g.

    Table 1
    11:11:10
    11:11:20
    11:11:30
    11:11:40
    11:11:50
    11:12:00

    Table 2
    11:11:05
    11:11:20
    11:11:35
    11:11:45
    11:11:48
    11:12:05

    The join will bring together the first record of each table as the join accepts records which are matching or less than 5 seconds from the record in table 1. If you take the 5th record in table 1 (11:11:50) then this will be joined to table 2 (11:11:45 and 11:11:48). I need the query to join all the matching records up and when there is multiple values for one record in table 1 then I need it to select the most up-to-date record from table 2 in the boundaries mentioned above.

    Thanks again for your help.

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry - I misread your problem. I'll have another look.

  5. #5
    Join Date
    Jun 2011
    Posts
    3

    Re:

    Thats not a problem, probably the way I described the issue in the first place. Any help would be greatly appreciated.

    Cheers

Tags for this Thread

Posting Permissions

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