My problem here is that the ID fetch by this query is not correct for amount 147.25 altough the min_date it fetch is correct:

the min_date queried is as follow:

4.58 = 2016-07-18
50.00 = 2016-07-22
147.25 = 2016-07-15

There ID should be this respectively:

4.58 = 3
50.00 = 5
147.25 = 2

But the actual ID is:

4.58 = 3
50.00 = 5
147.25 = 6

As you can see for amount "147.25" it is getting due_date 2016-07-22 when in the MIN function it got the correct one which is 2016-07-15, it is also getting the correct ID which should be 2 and not 6, Will anyone be able to help me with this as I cannot really see anymore what the problem is? since shouldn't it retrieve the appropriate ID based on my min_date when it got that correct from the start?

GROUP BYdoes not select rows from the database. It generates records using the values from the database. Each expression that appears in the SELECT clause of a query that also contains a GROUP BY clause is computed independent of the other expressions.

Think a little about it. Assuming it should work as you want, what values should the query return if you replace MIN() with another GROUP BY aggregate function? With AVG(), for example. Most probably, there isn't any row in the cb_paymentscheduledetail table having in column due_date the value returned by AVG(due_date). Or COUNT()? They doesn't even have the same type.

Take a look at this answer provided on a similar question to learn the correct way to write a query that returns the row having the minimum/maximum value from its group in a certain column. It can be expanded to accommodate six tables by INNER JOIN-ing them with the table from which the row is selected (table o in that answer).

If you cannot handle it this way, you can split your query into two smaller queries (you can even merge them later using subqueries): one query on paymentscheduledetail that selects MIN(due_date) and groups by date_entered and another query that uses the value returned by the first query to select the desired row(s). Take into account that there can be more than one row that have in due_date the minimum value.

Email codedump link for Wrong ID is retrieve during a select query with 6 joined tables