I have 4 work trays and I would like a report that gives me the name of each work tray, plus the oldest item of post in it, plus a couple more fields. It needs to be limited to 4 rows - one for each work tray.
So at the moment I have this:
SELECT WorkTray, MIN(Date) AS [OldestDate], RefNo, NameofItem
GROUP BY WorkTray,RefNo, NameofItem
ORDER BY WorkTray,RefNo, NameofItem
However when I run this it gives me every item in each work tray, eg a report 100s of items long - I just want it to be limited to 4 rows of data, one for each work tray:
Work Tray Date RefNo NameofItem
A 1/2/15 25 Outstanding Bill
B 5/5/18 1000 Lost post
C 2/2/12 17 Misc
D 6/12/17 876 Misc
So I'm sure I'm going wrong somewhere with my GROUP BY - but I can't see where.
Any advice would be very gratefully received.