# Beginner SQL Query Question

**URL:** <https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672>\
**Category:** Ignition\
**Created:** [July 14, 2020, 7:17am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672 "2020-07-14T07:17:54Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bob\_O](https://avatars.discourse-cdn.com/v4/letter/b/5e9695/32.png) [@Bob\_O](https://forum.inductiveautomation.com/u/Bob_O)\
**Post date:** [July 14, 2020, 7:17am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/1 "2020-07-14T07:17:54Z")

</div>

I’m playing around with reporting and I would like to get the record for each date between dates.

I have the StartDate and EndDate on calendars and I can retrieve data between the two selected dates but I don’t know how the grab only the last record for each date.

Below is the query I have so far.

SELECT transaction.t\_stamp,

transaction.AveAmps,

transaction.Random

FROM transaction

WHERE transaction.t\_stamp \>= ? AND transaction.t\_stamp \<= ?

ORDER BY transaction.t\_stamp

I’m testing at home on 8.0.10 but have an older version at work ~7.something

Any pointers appreciated.

Thanks,

---

<div class="post-metadata">

**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\
**Post date:** [July 14, 2020, 8:29am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/2 "2020-07-14T08:29:53Z")

</div>

It depends on the type of database you are using: MySQL, MS SQL, etc…

In MS SQL to get the last record use:

SELECT TOP 1 t\_stamp, AveAmps, Random  
FROM transaction  
WHERE t\_stamp \>= ? AND t\_stamp \<= ?  
ORDER BY t\_stamp desc

---

<div class="post-metadata">

**Author:** ![Bob\_O](https://avatars.discourse-cdn.com/v4/letter/b/5e9695/32.png) [@Bob\_O](https://forum.inductiveautomation.com/u/Bob_O)\
**Post date:** [July 14, 2020, 8:38am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/3 "2020-07-14T08:38:30Z")

</div>

> [@DavidWisely](#):
>
> SELECT TOP 1 t\_stamp, AveAmps, Random  
> FROM transaction  
> WHERE t\_stamp \>= ? AND t\_stamp \<= ?  
> ORDER BY t\_stamp desc

I'm using MySQL sorry for not stating that.

---

<div class="post-metadata">

**Author:** ![Bob\_O](https://avatars.discourse-cdn.com/v4/letter/b/5e9695/32.png) [@Bob\_O](https://forum.inductiveautomation.com/u/Bob_O)\
**Post date:** [July 14, 2020, 8:45am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/4 "2020-07-14T08:45:14Z")

</div>

Looks like the Select TOP 1 in My SQL is the same as LIMIT 1. I’ve tried this and get only the last record between the dates.

So if I had a range of 10 dates I would only retrieve one record not 10.

Hope that makes sense.

---

<div class="post-metadata">

**Author:** ![jonathan.taylor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jonathan.taylor/32/4048_2.png) [@jonathan.taylor](https://forum.inductiveautomation.com/u/jonathan.taylor)\
**Post date:** [July 14, 2020, 9:29am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/5 "2020-07-14T09:29:22Z")

</div>

Sounds like you’re also looking for the `GROUP BY` statement. Try `GROUP BY t_stamp` before your `ORDER BY` statement

---

<div class="post-metadata">

**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\
**Post date:** [July 14, 2020, 10:45am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/6 "2020-07-14T10:45:01Z")

</div>

Ok, that’s different to what I orginally thought you were after.  
As jonathan suggests, maybe something like this:

SELECT  
T.t\_stamp,  
T.AveAmps,  
T.Random  
FROM transaction T  
INNER JOIN  
(  
SELECT MAX(t\_stamp) max\_time  
FROM transaction  
GROUP BY date(t\_stamp)  
) AS D  
ON T.t\_stamp = D.max\_time

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 14, 2020, 10:51am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/7 "2020-07-14T10:51:37Z")

</div>

In t-SQL (Microsoft SQL Server), you can use:

```auto
SELECT *
FROM test
WHERE t_stamp IN
(SELECT MAX(t_stamp)
FROM test
GROUP BY FORMAT(t_stamp, 'yyyy-MM-dd'))

```

Or, untested:

```auto
SELECT transaction.t_stamp,
transaction.AveAmps,
transaction.Random
FROM transaction
WHERE t_stamp IN
(SELECT MAX(t_stamp)
 FROM transaction
 WHERE t_stamp >= ? AND t_stamp <= ?
 GROUP BY FORMAT(t_stamp, 'yyyy-MM-dd')
)
ORDER BY transaction.t_stamp

```

If [SQLines - Online SQL Conversion - SQL Scripts, DDL, Queries, Views, Stored Procedures, Triggers, Embedded SQL](http://www.sqlines.com/online) is anything to go by, apparently this will work as-is in MySQL as well (the only thing I expected to change would have been the ‘FORMAT’ part)

@DavidWisely I wonder if your method using the inner join is better for performance? 🤔

---

<div class="post-metadata">

**Author:** ![Bob\_O](https://avatars.discourse-cdn.com/v4/letter/b/5e9695/32.png) [@Bob\_O](https://forum.inductiveautomation.com/u/Bob_O)\
**Post date:** [July 14, 2020, 11:50am UTC](https://forum.inductiveautomation.com/t/beginner-sql-query-question/36672/8 "2020-07-14T11:50:59Z")

</div>

David’s code gets me what I was originally looking for. Now that I’m at work, going to try and add more improvements.  
Thanks All and I’m sure I’ll return.