SQL Query Question

Ignition
1

I have two tables that I need to join data from. The result returns several entries of the same serial number. How do I structure my query to only see the latest entry for each serial number?
I am sorting by a date range to pull in the data for a set period of time.

2

You probably should include a little more information, but you can limit results by:

  1. MySQL use LIMIT 1, SQL TOP 1
  2. Use DISTINCT

Search the web for those terms and you should see a ton of results.

3

You can usually also create a subquery.

SELECT SerialNumber,
    (
        SELECT MAX(Val)
        FROM Table2
        WHERE Table2.SerialNumber = Table1.SerialNumber
    ) AS Val
FROM Table1
4

Thank you jlandwerlen and Sanderd17. Here is some more detail.

Tablel 1
Log Time Identifier Serial Number
May 4, 2019 6:53AM Tool 1 ABC123
May 4, 2019 6:55AM Tool 1 ABC123
May 4, 2019 7:22AM Tool 1 ABC123
May 4, 2019 7:30AM Tool 1 ABC123
May 4, 2019 8:53AM Tool 1 ABC123
May 14, 2019 8:53AM Tool 2 DEF456
May 15, 2019 8:53AM Tool 3 GHI789
May 16, 2019 8:53AM Tool 3 GHI789
Table 2
Serial Number Next Check Date
ABC123 May 4, 2020
ABC123 May 4, 2020
ABC123 May 4, 2020
ABC123 May 4, 2020
ABC123 May 4, 2020
DEF456 May 14, 2020
GHI789 May 15, 2020
GHI789 May 16, 2020
Desired Result
Log Time Identifier Serial Number Next Check Date
May 4, 2019 8:53AM Tool 1 ABC123 May 4, 2020
May 14, 2019 8:53AM Tool 2 DEF456 May 14, 2020
May 16, 2019 8:53AM Tool 3 GHI789 May 16, 2020
5

Here is one way.

SELECT
  [Log Time],
  [Identifier],
  [Serial Number],
  (SELECT TOP 1
    [Next check Date]
  FROM [table 2]
  WHERE [table 2].[Serial Number] = [t1].[Serial Number]
  ORDER BY [Next Check Date] DESC)

FROM [Table 1] t1

WHERE [Log Time] = (SELECT
  MAX([Log Time])
FROM [table 1]
WHERE t1.[identifier] = [table 2].[identifer])
6

As the tables grow using a sub-select within the select statement will start slowing down.

Iā€™d perform a sub-table join as all your indexes continue to be used:

select t1.serialno, t1.col1, t2.id, t2.serialno, t2.col1
from table1 t1
	inner join (
				select max(id),serialno 
					from table2 
				group by serialno) t2a on t2a.serialno = t1.serialno
	inner join table2 t2 on t2a.id = t2.id

Edit:
Just saw the sample tables you provided, massaging my original response:

select t1.[serial number], t1.[Log Time], t1.Identifier, t2.[serial number], t2.[Next Check Date]
from 
	(select max(Log Time), [serial number], identifier from table1 group by [serial number], identifier) t1
	inner join (
				select max([Next Check Date]),[serial number] 
					from table2 
				group by [serial number]) t2 on t2.[Serial Number] = t1.[Serial Number]
1 Like