I have a database which I need to get into from two tables on. Let's say that they are called 'scan' and 'detail.'
Ok, so some data:
I need to return a reset set that has only the most recent result. For example:
I have tried to do this with SQL and cannot figure it out. I cannot use a subselect since I am using MySQL 4.0.24 and cannot upgrade at this time. I need a query that will work without the subselect. I have tried to do an inner join and cannot get that to work properly because I do get the right date and computer, but the installed info does not match the date.
Here is what I have tried:
select distinct s.computer, d.isinstalled, max(s.scandate) from scan s inner
join scan s2 using(scandate), detail d where s.id = d.scanid group by
Can you help me?
In the example given, there is no unique join condition between the "scan" and "detail" tables. I assume that the "scan" row with the latest date corresponds to the `detail` row with the highest auto-incremented "id" for the correlated "scanId." For this particular query, if that assumption is valid, we can still get the desired results. For other queries, it would be helpful to have some field(s) to absolutely match a row in "detail" to a row in "scan."
We're going to use the method mentioned by user Csaba Gabor in the comments section below 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field.
By LEFT JOINing the "scan" table to itself with the left "scandate" less than the right one, and checking for a right value of NULL, we'll end up with just the rows that have no greater "scandate" for that "id." We'll do the same with the "detail" table, using "id" in the inequality:
SELECT scan.*, detail.* FROM scan LEFT JOIN scan s2 ON (scan.computer = s2.computer AND scan.scandate
Dig Deeper on Open source databases
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes.continue reading
A MySQL expert describes two ways that the multi-master support can be used.continue reading
An expert describes where to find information on detection deadlock algorithms in MySQL.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.