Search for the maximum value of the difference in the year

Question

I have two tables here

BIODATA ID NAME 1 A 2 B YEAR ID JOIN YEAR GRADUATE YEAR 1 1990 1991 2 1990 1993 

I already use

 select NAME, max(year(JOIN_YEAR) - year(GRADUATE_YEAR)) as MAX from DATA_DIRI right join DATA_KARTU ON BIODATA.ID = YEAR.ID; 

but the result was as follows:

 +--------+------+ | NAME | MAX | +--------+------+ | A | 3 | +--------+------+ 

I have already tried many different connections, but I still can’t find how NAME will be “B”. Can anybody help me? Thank you very much before

Answer 1

If you use an aggregate and a non-aggregate in a selection set at once, then the row used for the non-aggregate field is essentially randomly selected.

In principle, how much it works max - it collects all the rows for each group on request (if there is no group, all of them), calculates max and puts it in the result.

But since you also put in a non-aggregate field, you need a value for that - so SQL does just select a random row. You might think, "well, why doesn't he select the same row as he did?" but what if you used avg or count? They do not have a string associated with it, so the best thing is to choose it at random. That is why this behavior exists at all.

What you need to do is use a subquery. Something like select d1.id from data_diri d1 where d1.graduate_year - d1.join_year = (select max(d2.graduate_year - d2.join_year from data_diri d2))