# SQL Query Question

**URL:** <https://forum.inductiveautomation.com/t/sql-query-question/34440>\
**Category:** Ignition\
**Created:** [May 5, 2020, 1:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440 "2020-05-05T13:15:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![leonardjamiej](https://avatars.discourse-cdn.com/v4/letter/l/e19b73/32.png) [@leonardjamiej](https://forum.inductiveautomation.com/u/leonardjamiej)\
**Post date:** [May 5, 2020, 1:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/1 "2020-05-05T13:15:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [May 5, 2020, 1:59pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/2 "2020-05-05T13:59:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [May 5, 2020, 2:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/3 "2020-05-05T14:09:12Z")

</div>

You can usually also create a subquery.

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

```

---

<div class="post-metadata">

**Author:** ![leonardjamiej](https://avatars.discourse-cdn.com/v4/letter/l/e19b73/32.png) [@leonardjamiej](https://forum.inductiveautomation.com/u/leonardjamiej)\
**Post date:** [May 5, 2020, 2:22pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/4 "2020-05-05T14:22:33Z")

</div>

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 |

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [May 5, 2020, 4:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/5 "2020-05-05T16:36:29Z")

</div>

Here is one way.

```auto
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])

```

---

<div class="post-metadata">

**Author:** ![Stevenson.Yuan](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@Stevenson.Yuan](https://forum.inductiveautomation.com/u/Stevenson.Yuan)\
**Post date:** [May 5, 2020, 4:51pm UTC](https://forum.inductiveautomation.com/t/sql-query-question/34440/6 "2020-05-05T16:51:22Z")

</div>

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:

```auto
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:

```auto
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]

```