CODEWITHSUNDEEP
sqldate
Haydar Ali Ismail
Haydar Ali Ismail

Reputation: 411

Finding the maximum value of year difference

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 became:

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

I already try a lot of different kind of joins but I still can't find how the NAME to be "B". Anyone can help me? Thanks a lot before

Upvotes: 4

Views: 85

Answers (1)

Patashu
Patashu

Reputation: 21773

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

Basically, how max works is this - it gathers all rows for each group by query (if there is no group by, all of them), calculates the max and puts that in the result.

But since you also put in a non-aggregate field, it needs a value for that - so what SQL does is just pick a random row. You might think 'well, why doesn't it pick the same row max did?' but what if you used avg or count? These have no row associated with it, so the best it can do is pick randomly. This is why this behaviour exists in general.

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))

Upvotes: 2

Related Questions