Second to Last Row

Posted on 2011-09-20
Last Modified: 2012-05-12
Does Access have something similar to the Row Partition function in Oracle where you can rank or place a row number based on a column you want to order by?  For example, I have data in a table as listed below.  I can get the Max Order Date and the Min Order Date per AcctNumber easily but how would I get the second to last Order Date?  In this case it would be OrderID number 5 for AcctNumber 64.  OrderID is the primary key.  Also, not every Order ID will have a second to last order, (there could only be one).  In that case, I would still want the last and only Order ID returned.  Ideally the query results would look like this:


3	64	   9/2/2011 1:48 PM
4	64	   9/2/2011 2:19 PM
5	64	   9/2/2011 2:29 PM
6	64	   9/2/2011 2:30 PM

Open in new window

Question by:error_prone
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
ID: 36570660
A simple self-join will do:

SELECT o1.AcctNumber, Max(o1.OrderDate) AS LastOrderDate, Max(o1.OrderID) AS LastOrderID, 
    Max(o2.OrderDate) AS SecToLastOrderDate
FROM tblOrders o1 LEFT JOIN 
    tblOrders o2 ON o1.OrderDate > o2.OrderDate AND o1.AcctNumber = o2.AcctNumber
GROUP BY o1.AcctNumber

Open in new window

Replace with your actual table/column names.  Note that Access will be unable to show that in the GUI query design view, but can show the SQL view.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 36570784
I think this works also ... and will show in the Query Design Grid:

SELECT TOP 1 o1.AcctNumber, o1.OrderDate, o1.OrderID, (SELECT TOP 1  o2.OrderDate
FROM tblOrders AS o2
WHERE o2.OrderID<o1.OrderID
ORDER BY o2.OrderID DESC) AS 2ndLastOD
FROM tblOrders AS o1
ORDER BY o1.OrderDate DESC;

AcctNumber      OrderDate                   OrderID      2ndLastOD
64                     09-02-2011 14:30:00      6             09-02-2011 14:29:00
LVL 75
ID: 36570789
And it covers this case also:

"Also, not every Order ID will have a second to last order, (there could only be one).  In that case, I would still want the last and only Order ID returned.  "

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

LVL 92

Expert Comment

by:Patrick Matthews
ID: 36570833
Just to confirm, my suggestion covers the case of a sole order just splendidly :)
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36570843

Your query only works if there is but one AcctNumber in the table.  If there is >1 AcctNumber, you get the correct result for just one of the AcctNumber values.



Author Closing Comment

ID: 36570844
LVL 75
ID: 36570845
Sorry ... I didn't mean to imply it did not.

But ... sorry bout not working in the grid :-)

LVL 75
ID: 36570852
I might have missed that minor detail ...

LVL 75
ID: 36572022
My solution does not cover part of what you want - showing results for each AcctNumber as Patrick noted, thus really is not a correct answer. So ... you should probably hit the Request Attention button :-)

LVL 92

Expert Comment

by:Patrick Matthews
ID: 36573462
Any Mods who may be reviewing this:

I see no need to alter the disposition here; if the Asker found MX's comment to be helpful, then a split is justified.  MX's comment is not necessarily wrong, it's just limited in its application.



Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question