Select query (with DISTINCT) yields multiple results 1

[karlomutschler]

Hi,
The query below should display the contract number and the last invoice date with the corresponding amount.
However, the result lists all the dates and corresponding amounts.

SELECT DISTINCT con.contract_id
,MAX(cot.revenue_date)
,cot.revenue_amount
FROM contract con
INNER JOIN contract_turnover cot ON con.contract_id = cot.contract_id
GROUP BY con.contract_id, cot.revenue_amount ;

Result:
contract_id revenue_date revenue_amount
1000055 2000-05-01 5,01
1000055 2000-06-01 14,18
1000055 2000-07-01 13,07
1000055 2000-08-01 9,40
1000055 2000-09-01 16,06
1000055 2000-10-01 12,10

Desired result:
1000055 2000-10-01 12,10

TIA
Kind regards.
Karlo

 

[Tim1]

Hi Karlo,

The query as written is working correctly, because it is returning lots of DISTINCT MAX dates. Don't forget, DISTINCT works across the entire query, not just the column it is put against.

What you actually require is :

SELECT con.contract_id,
MAX(cot.revenue_date),
cot.revenue_amount
FROM contract con
INNER JOIN contract_turnover cot ON con.contract_id = cot.contract_id
GROUP BY con.contract_id, cot.revenue_amount ;

Tim

 

[karlomutschler]

Thank you Tim,
works perfectly

Enjoy your day
Kind regards
Karlo