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
I need all the newest ballast state based on an Area
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?
Hi
I know how to send it in a topic, but I need help making the request
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
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
SELECT State FROM table_name WHERE Area = 15 ORDER BY created_at DESC LIMIT 1
Something like above should work.
Hi
Thanks for your answers, I know this is not a node red question, but hoping you could help.
I did try with this
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)
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.
Hmm it's supposed to be the most recent record for each area (number)
But without any data, it was hard to test it!
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
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)
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!
you are awesome
I am not sure how to read the code, but it works perfect 
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.
Thanks for the explaining
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
