Group by MOST RECENT date

[vangundy]

I have one table called Equipment and a second table called Reserve. The relationship between
the two tables is TicketNo. Here is my query:

SELECT DISTINCT [Reserve].[TicketNo], [Equipment].[TicketNo], [Reserve].[Date1], [Reserve].[Date2] FROM Equipment LEFT JOIN Reserve ON [Equipment].[TicketNo]=[Reserve].[TicketNo];

In the Reserve table I have three records:

TicketNo Date1 Date2
12345 27/05/05 27/05/05
22222 23/05/02 23/05/05
12345 30/05/05 31/05/05

As you can see, ticket 12345 displays twice.

Is there a way to modify my query to group by ticket number with the most recent date:
TicketNo Date1 Date2
12345 27/05/05 27/05/05
22222 23/05/02 23/05/05

 

[cmmrfrds]

This will work if the max for date1 and date2 is in the same record.
SELECT [Reserve].[TicketNo],
Max([Reserve].[Date1]) as Date1,
Max([Reserve].[Date2]) as Date2
FROM Equipment
LEFT JOIN Reserve
ON [Equipment].[TicketNo]=[Reserve].[TicketNo]
Group By ]=[Reserve].[TicketNo]

If you want the max of date2 and then drag along date1, it will be necessary to do in 2 steps.
Call this query qryDate2Max
SELECT [Reserve].[TicketNo],
Max([Reserve].[Date2]) as Date2
FROM Reserve
Group By ]=[Reserve].[TicketNo]

Use the first query to join to the second query.
SELECT [Reserve].[TicketNo],
[Reserve].[Date1] as Date1,
[Reserve].[Date2] as Date2
FROM Equipment
LEFT JOIN Reserve
ON [Equipment].[TicketNo]=[Reserve].[TicketNo]
INNER JOIN qryDate2Max
ON qryDate2Max.Date2 = [Reserve].[Date2]
and qryDate2Max.[TicketNo]= Reserve.TicketNo

 

[ByteMyzer]

You might also try this query:
[tt]
SELECT DISTINCT R1.TicketNo, Equipment.TicketNo, R1.Date1, R1.Date2
FROM Equipment LEFT JOIN Reserve AS R1 ON Equipment.TicketNo=R1.TicketNo
WHERE R1.Date1=
(SELECT Max(R2.Date1)
FROM Reserve AS R2
WHERE R2.TicketNo=R1.TicketNo
AND R1.Date2=
(SELECT Max(R3.Date2)
FROM Reserve AS R3
WHERE R3.TicketNo=R1.TicketNo AND R3.Date1=R1.Date1
;
[/tt]

 

[vangundy]

I have a record in Equipment that does not yet exist in Reserve. How can I get this record to display in the query you mentioned:
(So far so good thank you!!!)

SELECT DISTINCT R1.TicketNo, Equipment.TicketNo, R1.Date1, R1.Date2
FROM Equipment LEFT JOIN Reserve AS R1 ON Equipment.TicketNo=R1.TicketNo
WHERE R1.Date1=
(SELECT Max(R2.Date1)
FROM Reserve AS R2
WHERE R2.TicketNo=R1.TicketNo
AND R1.Date2=
(SELECT Max(R3.Date2)
FROM Reserve AS R3
WHERE R3.TicketNo=R1.TicketNo AND R3.Date1=R1.Date1
;

 

[PHV]

Why not simply this ?
SELECT R.TicketNo, E.TicketNo, Max(R.Date1) AS MaxOfDate1, Max(R.Date2) AS MaxOfDate2
FROM Equipment AS E LEFT JOIN Reserve AS R ON E.TicketNo = R.TicketNo
GROUP BY R.TicketNo, E.TicketNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vangundy]

Thank you all... with your query instead of the latest day can you display the most recent date to todays date... So it will not show records where the date1 is less than todays date... and show the record where the date1 is equal to todays date or the date1 that is closest to todays date...

Thank you for all your help! You guys rock!!!!

 

[PHV]

Create a saved query named, say, qryReserve:
SELECT TicketNo, Min(Date1) AS myDate1
FROM Reserve
WHERE Date1 >= Date()
GROUP BY TicketNo;

And now your query:
SELECT R.TicketNo, E.TicketNo, R.Date1, R.Date2
FROM Equipment AS E LEFT JOIN (Reserve AS R INNER JOIN qryReserve AS Q ON R.TicketNo = Q.TicketNo AND R.Date1 = Q.myDate1) ON E.TicketNo = R.TicketNo;

You may even try this single query (ie without the saved one):
SELECT R.TicketNo, E.TicketNo, R.Date1, R.Date2
FROM Equipment AS E LEFT JOIN (Reserve AS R INNER JOIN (
SELECT TicketNo, Min(Date1) AS myDate1 FROM Reserve WHERE Date1 >= Date() GROUP BY TicketNo
) AS Q ON R.TicketNo = Q.TicketNo AND R.Date1 = Q.myDate1) ON E.TicketNo = R.TicketNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244