How to select one record per value in inner join

INeedHelpPlz · #1 (**permalink**) 06-09-11, 16:56

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.

christyxo · #2 (**permalink**) 06-10-11, 07:01

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)));

INeedHelpPlz · #3 (**permalink**) 06-10-11, 10:35

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.