Problem retrieveing multiple fields with MAX

Robert Kaplan

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Problem retrieveing multiple fields with MAX

I have two master/detail tables as follows

Master:

m_id m_name

Detail

d_id d_mid d_date d_amount (m_id and d_mid are the link fields)

I'm trying to develop a query to obtain

m_id / m_name / highest d_date / d_amount corresponding to the highest
d_date

a query

SELECT M_ID, M_NAME, MAX(D_DATE) AS DATE, D_AMOUNT AS AMOUNT
FROM MASTER, DETAIL
WHERE M_ID = D_MID
GROUP BY M_ID

returns the correct (highest) date, but the amount is not the amount that
corresponds to that high date. If I have

1/1/2000 $1000
1/5/2000 $2000
1/10/2000 $3000

I get 1/10/2000 $1000

Any ideas how to solve this? Thanks.

Robert

M.H. Avegaar

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Problem retrieveing multiple fields with MAX

This has to be done in a nested query (probably slow):

SELECT MASTER.M_ID, MASTER.M_NAME, DETAIL.D_DATE, DETAIL.D_AMOUNT
FROM MASTER, DETAIL
WHERE MASTER.M_ID = DETAIL.D_MID
AND DETAIL.D_DATE IN (
SELECT MAX(DETAIL_2.D_DATE)
FROM DETAIL DETAIL_2
WHERE DETAIL_2.D_MID = DETAIL.D_MID
GROUP BY DETAIL_2.D_MID
)

"Robert Kaplan" <rkap...@iamerica.net> schreef in bericht
news:Tg835.48$kDh.393357@dca1-nnrp1.news.digex.net...

Quote

> I have two master/detail tables as follows

> Master:

> m_id m_name

> Detail

> d_id d_mid d_date d_amount (m_id and d_mid are the link fields)

> I'm trying to develop a query to obtain

> m_id / m_name / highest d_date / d_amount corresponding to the highest
> d_date

> a query

> SELECT M_ID, M_NAME, MAX(D_DATE) AS DATE, D_AMOUNT AS AMOUNT
> FROM MASTER, DETAIL
> WHERE M_ID = D_MID
> GROUP BY M_ID

> returns the correct (highest) date, but the amount is not the amount that
> corresponds to that high date. If I have

> 1/1/2000 $1000
> 1/5/2000 $2000
> 1/10/2000 $3000

> I get 1/10/2000 $1000

> Any ideas how to solve this? Thanks.

> Robert

Other Threads

1. How to use one field to have multiple field values go to multiple lines

2. Q: Retrieveing information about a tables referential constraints

3. Multiple queries displaying multiple fields

4. Multiple Fields lookup with index problem

5. problems with multiple Array Fields in Tclientdataset

6. Problem: lookup list & multiple key fields

7. Problem with Master Detail with multiple fields

8. exceeded max max user count

9. lookup field with multiple key fields

10. How do I create Multiple Field Lookup Fields