0

I am having an issue with my multi join SQL statement, where I need to get the last entries of the table and not the first. I have 3 tables that I try to query based on this statement:

SELECT DISTINCT i.id,i.hz,i.v,i.t,i.u,i.temp, i.c1, s.server, s.hostname, h.codes,s.cpus FROM `dc_servers` AS s INNER JOIN `dc_hardware` AS h ON  s.server = h.server INNER JOIN `dc_systemusage` AS i ON s.server = i.server GROUP BY i.server

The tables dc_servers & dc_hardware only has 1 row per server, however the table dc_systemusage has many rows with the same server, as new info is being added.

When I run the query, I get the first entries from the dc_systemusage, but I need to get the latest entries from that table, for me it sounds like I need an ORDER BY, however if I add that to the end of the query like this:

SELECT DISTINCT i.id,i.hz,i.v,i.t,i.u,i.temp, i.c1, s.server, s.hostname, h.codes,s.cpus FROM `dc_servers` AS s INNER JOIN `dc_hardware` AS h ON  s.server = h.server INNER JOIN `dc_systemusage` AS i ON s.server = i.server GROUP BY i.server ORDER BY i.id DESC

then I am just ordering the result, which is not what I am looking for.

I hope someone can guide me in the right direction as for how I can get the latest rows and not the first from the table dc_systemusage

I hope I have provided the needed information to guide me, else please do let me know and I will add whatever is neeeded.

Thank you all.

HDIK
  • 33
  • 6
  • 3
    What is there in your `dc_systemusage` table that identifies the latest entry? A timestamp? You will need to build a query to get its max value by server. – Chris Maurer Dec 08 '22 at 15:05
  • @ChrisMaurer - There is the ID as AUTO_INCREMENT, the `i.id`, there is also a timestamp, but figured it would be easier to use the id field – HDIK Dec 08 '22 at 15:31

1 Answers1

1

You can find dc_systemusage lastest ids in subquery and use with WHERE ... IN

SELECT i.id,i.hz,i.v,i.t,i.u,i.temp, i.c1, s.server, s.hostname, h.codes,s.cpus
FROM `dc_servers` AS s
INNER JOIN `dc_hardware` AS h ON  s.server = h.server
INNER JOIN `dc_systemusage` AS i ON s.server = i.server
WHERE i.id IN (SELECT max(dc_systemusage.id) FROM dc_systemusage GROUP BY dc_systemusage.server)

and check a great answer at https://stackoverflow.com/a/3800572/7174186

buithienquyet
  • 351
  • 1
  • 6
  • Thank you so much, was what I was looking for! I did search and query other questions to see if there was what I was looking for, but I guess I used wrong search terms to get the right answer. But thank you very much. And as a bonus, the query is also much faster than what it was before which is a huge bonus. – HDIK Dec 08 '22 at 15:55