I need my query to show 1 record  but not the one with the highest date.

SELECT Master.MasterID, Detail.DetailDate, Detail.DetailBalance
FROM Master, Detail
WHERE
Master.MasterID = Detail.MasterID
GROUP BY Master.MasterID
ORDER BY Detail.DetailDate DESC

Data for detail is like this
DetailID  MasterID  DetailDate  DetailBalance
1            1             6/2/2015    200
2            1             6/3/2015    300
3            1             6/1/2015    100

result is
MasterID  DetailDate  DetailBalance
1             6/2/2015     200.00

I want it to show
MasterID  DetailDate  DetailBalance
1             6/3/2015     300.00

Thanks for any help
Brent

Brent


In this case you've picked the second item, is that always the case, what if detail contains 4 rows, 7 rows etc.

Is the criteria the biggest detail balance which is what you've show here

More info needed before an answer can be attempted

Roy Lambert

Brent Shelton

<<
SELECT Master.MasterID, Detail.DetailDate, Detail.DetailBalance
FROM Master, Detail
WHERE
Master.MasterID = Detail.MasterID
GROUP BY Master.MasterID
ORDER BY Detail.DetailDate DESC
>>
When you put "order by" you define the order to show and does not matter if it will show one or more records.
You have to use more criteria for data filtering (in the where clause) or add more fields in the order by.

Like Roy said, we need more details to help you.

Jose


Whilst reading your post I had a thought - is this the old problem with which non grouped data is returned from a GROUP BY

Roy Lambert

>>
In this case you've picked the second item, is that always the case, what if detail contains 4 rows, 7 rows etc.
Is the criteria the biggest detail balance which is what you've show here
More info needed before an answer can be attempted
<<

Sorry, in my original post I meant to say I want to show one record for each MasterID with the detail record being the one with the highest date.  When records are being entered, I am not allowing a duplicate date.

Brent


>In this case you've picked the second item, is that always the case, what if detail contains 4 rows, 7 rows etc.
>Is the criteria the biggest detail balance which is what you've show here
>More info needed before an answer can be attempted
><<
>
>Sorry, in my original post I meant to say I want to show one record for each MasterID with the detail record being the one with the highest date. When records are being entered, I am not allowing a duplicate date.

OK this is the GROUP BY problem. GROUP BY does not guarantee which details are returned apart from the grouped ones. I think its based on the physical order of the rows but it could be anything. Its a question that's been asked quite a few times. Just search this news group for GROUP BY

Roy Lambert