# Select from Mysql

**URL:** <https://discourse.nodered.org/t/select-from-mysql/49446>\
**Category:** General\
**Created:** [7 August 2021 08:49 UTC](https://discourse.nodered.org/t/select-from-mysql/49446 "2021-08-07T08:49:04Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 08:49 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/1 "2021-08-07T08:49:04Z")

</div>

Hi

I need to get data from my sql database, but i can't see how to get the newest values.

my database looks like this

 ![mysql](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/d/adac6dd5b87f6b4133a7f5730d4f8449e6a0e006.jpeg)

I need all the newest ballast state based on an Area

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 August 2021 09:07 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/2 "2021-08-07T09:07:47Z")

</div>

Construct your SQL statement and put it into `msg.topic` then send it to the mySQL node.

Do you know the SQL syntax to select the newest item from a table?

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 09:18 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/3 "2021-08-07T09:18:35Z")

</div>

Hi  
I know how to send it in a topic, but I need help making the request

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 August 2021 09:20 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/4 "2021-08-07T09:20:03Z")

</div>

Well this is a node-red forum, not really a SQL forum however a quick search reveals the answer...

"Retrieve the most recent record from a database" [Retrieve the most recent record from a database](https://www.paulsilver.co.uk/code/sql_most_recent_record.php)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 09:43 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/5 "2021-08-07T09:43:32Z")

</div>

Something along these lines?

msg.topic = select \* from table\_name a inner join (select area, max(created\_at) as ts from table\_name group by area) as b on a.area = b.area and a.created\_at = ts

[https://stackoverflow.com/questions/43448908/sql-to-get-max-value-from-each-group](https://stackoverflow.com/questions/43448908/sql-to-get-max-value-from-each-group)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [7 August 2021 10:13 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/6 "2021-08-07T10:13:38Z")

</div>

```auto
 SELECT State FROM table_name WHERE Area = 15 ORDER BY created_at DESC LIMIT 1

```

Something like above should work.

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 18:03 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/7 "2021-08-07T18:03:47Z")

</div>

Hi

Thanks for your answers, I know this is not a node red question, but hoping you could help.

I did try with this

```auto
SELECT State, Ballast, Area, created_at FROM DC46BN59 WHERE Area = 15 ORDER BY created_at DESC

```

But i only want one state for each ballast (the newest one)  
with this code it lists all ballast in database for area 15

As you can see in the picture above I have ballast 9 listed many times, so i only want the newest value for ballast 9 (Offline)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 18:13 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/8 "2021-08-07T18:13:24Z")

</div>

> [@zamzon](#):
>
> But i only want one state for each ballast (the newest one)

Did you try my suggestion above? What does it return?

If you only want the latest state for a given area, E1cid's suggestion should be fine. You omitted the "LIMIT 1" bit.

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 18:21 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/9 "2021-08-07T18:21:07Z")

</div>

> [@jbudd](#):
>
> msg.topic = select \* from table\_name a inner join (select area, max(created\_at) as ts from table\_name group by area) as b on a.area = b.area and a.created\_at = ts

I get a strange result, I can not really figure it out. but that is not true.

 ![Udklip](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/09aaa51ecdc8008306e135f7f1cfcb6bb7aee8b5.jpeg)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 18:27 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/10 "2021-08-07T18:27:33Z")

</div>

Hmm it's supposed to be the most recent record for each area (number)  
But without any data, it was hard to test it!

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 18:33 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/11 "2021-08-07T18:33:23Z")

</div>

Okay, but where do I inset my area to receive data from ?  
I only want data for one area, and all the newest recordings, one pr. ballast

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [7 August 2021 18:38 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/12 "2021-08-07T18:38:22Z")

</div>

maybe you need some grouping first and get the MAX from that group

`SELECT * FROM DC46BN59 WHERE created_at IN (SELECT MAX(created_at) FROM DC46BN59 WHERE Area = 15 GROUP BY Ballast)`

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 18:45 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/13 "2021-08-07T18:45:08Z")

</div>

`SELECT a.state, a.ballast, a.area, a.created_at FROM DC46BN59 a inner join (select ballast, max(created_at) as ts from DC46BN59 group by ballast) as b on a.ballast = b.ballast and a.created_at = ts and a.area = 15`  
?  
Edit - not at all sure about that and a.area = 15...  
Should be where a.area = 15?

ps Sorry I never learned to type sql keywords in uppercase!

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [7 August 2021 19:08 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/14 "2021-08-07T19:08:32Z")

</div>

you are awesome

I am not sure how to read the code, but it works perfect 🙂

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 20:31 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/15 "2021-08-07T20:31:56Z")

</div>

I will try and explain - starting with the inner sql query.

**select max(created\_at)** will retrieve just one record, the most recently created.

**select ballast, max(created\_at) as ts group by ballast** tells it to output the most recently created record _for each value of ballast_.  
Note that you can't have **select area, ballast, max(created\_at) group by ballast**, it only works for the field you group by and any aggregated fields like max(created\_at)

So to get the rest of the fields at that time you join the table to the result of the inner query:  
**select \* from table inner join (select ballast, max(created\_at) as ts from table group by ballast) as b**  
The ON clause tells it how to connect the inner and outer sql results  
**on a.ballast = b.ballast and a.created\_date = ts**

(ts is an alias for the aggregate field, a and b are aliases for the table name)

It only works if there are no duplicate records with the same values of ballast and created\_at.

---

<div class="post-metadata">

**Author:** ![zamzon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zamzon/32/15521_2.png) [@zamzon](https://discourse.nodered.org/u/zamzon)\
**Post date:** [9 August 2021 21:07 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/16 "2021-08-09T21:07:45Z")

</div>

Thanks for the explaining 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [23 August 2021 21:07 UTC](https://discourse.nodered.org/t/select-from-mysql/49446/17 "2021-08-23T21:07:46Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.