Trying to Get the Earliest Date using MIN()

Question

SELECT ID, AppID, Description, Min([Transaction Date]) AS 'Transacton Date'
FROM AppProsHist
WHERE [Description]='Non-Final Rejection'
GROUP BY ID, AppID, Description

I thought this would allow for only the first (earliest) transaction date to be shown in my table, however, this still shows each transaction date. Is there a way to alter this so that I receive just one Date for the ID, AppID, Description?

No I need the other fields, but I want just one date to come up. So all that is above is correct, however I want only the first/earliest date to be shown. — Johnfun10, Feb 17 '16 at 19:56

score 2 · Accepted Answer · edited Feb 17 '16 at 20:48

2

You should use ORDER BY and LIMIT 1 to get the first record of the ordered set. ASC or DESC while ordering will set the direction in which to sort. So try

SELECT TOP 1 ID, AppID, Description, Min([Transaction Date]) AS TransactionDate
FROM AppProsHist
WHERE [Description]='Non-Final Rejection'
GROUP BY ID, AppID, Description
ORDER BY TransactionDate DESC, ID

edited Feb 17 '16 at 20:48

Gordon Linoff

1,242,037
58
646
786

answered Feb 17 '16 at 20:24

Nataliegry

36
6

Access gives me a Popup error saying "syntax error in ORDER BY clause" then highlights "LIMIT". What do I do to fix this? – Johnfun10 Feb 17 '16 at 20:29
It's generally a bad idea to use spaces in aliases. So i updated my answer with the new alias `TransactionDate` – Nataliegry Feb 17 '16 at 20:34
Oh, sorry sorry, there was a typo in my code, I updated it again – Nataliegry Feb 17 '16 at 20:43
I think the issue I am having has to do with the "LIMIT" coming after "DESC". That's what the Syntax error keeps popping up for. Do you have any ideas how to fix the error? – Johnfun10 Feb 17 '16 at 20:47

Trying to Get the Earliest Date using MIN()

1 Answers1