Pulling two most recent values (MAX)

[AquaLady42]

This seems like it should be simple, but I can't figure it out! (Sorry, I am a newbie!)



I have a table called APPS that contains a number of fields:
RECORDNUM = the customer number assigned
CAPPNUM = the number assigned to an application
PROCDATE = the date of an application (of which there may be many for an individual RECORDNUM)

Basically the end result I want is to display a record number with the most recent application date (easy enough using the MAX function) but also the 2nd most recent application number.

Example:
12345 6/9/11 6/5/11

I can't figure out for the life of me what to put to get the 2nd newest application date ... I have tried to copy a few examples I found, but they seem to be more complicated than I need.

Any help? I APPRECIATE it a TON!

[pbaldy]

Depending on the final goal, this type of thing:

http://support.microsoft.com/kb/153747/en-us

or this type of thing, with code to limit to 2:

http://allenbrowne.com/func-concat.html

[alansidman]

Expanding on Paul's suggestion and using this site

http://allenbrowne.com/subquery-01.html
try:

SELECT APPS.RecordNum, APPS.Cappnum, APPS.procdate
FROM APPS
WHERE (((APPS.procdate) In (SELECT TOP 2 procdate FROM APPS AS Dupe WHERE Dupe.RecordNum = APPS.RecordNUm)))
ORDER BY APPS.RecordNum, APPS.procdate DESC;

Alan

[AquaLady42]

Thanks for the suggestion but I am still having difficulty. I tried your query and it just sat there for hours without progressing to the "processing" stage. The table is not that large and I have never had anything take that long to even start running. Any ideas on why that may be?

Thanks again for your help.

[alansidman]

Here is the db I created from the data you showed in the OP. Open it and compare the SQL statement and the QBE to what you have in your db. Mine works so I know the SQL statement is correct.

Suggest you run a compact and repair before you do anything and see if that helps.

Alan