• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Second to Last Row

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:

ACCTNUMBER    LAST_ORDER_DT    LAST_ORDER_ID    SECOND_TO_LAST_ORDER_DT  



ORDERID	ACCTNUMBER ORDERDATE
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

0
error_prone
Asked:
error_prone
  • 5
  • 4
2 Solutions
 
Patrick MatthewsCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.  "

mx
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Patrick MatthewsCommented:
Just to confirm, my suggestion covers the case of a sole order just splendidly :)
0
 
Patrick MatthewsCommented:
MX,

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.

:)

Patrick
0
 
error_proneAuthor Commented:
Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... I didn't mean to imply it did not.

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I might have missed that minor detail ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
error_prone:
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 :-)

mx
0
 
Patrick MatthewsCommented:
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.

:)

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now