MySQL INNER JOIN, get the last entries, not the first-CodePudding

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.

CodePudding user response：

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