473,883 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Last 3 records by group

46 New Member
How can I retrieve the last 3 records from the following table. The table has the following fields and sample data:

ClientID TransactionID TransactionDate

1 6511216 01/02/2012
1 5332573 18/05/2011
1 9849528 11/02/2012
1 5374530 24/08/2010
1 5711675 26/04/2009
1 4001184 01/09/2011
2 9087526 15/07/2011
2 6524824 06/08/2009
2 5376892 26/07/2011
2 5327891 28/10/2008
2 6423568 11/11/2011
2 5379827 16/06/2012

the results should be (order by TransactionDate ):

ClientID TransactionID TransactionDate

1 4001184 01/09/2011
1 6511216 01/02/2012
1 9849528 11/02/2012
2 5376892 26/07/2011
2 6423568 11/11/2011
2 5379827 16/06/2012

Note: I can't use 'Select Top 3' a. because of the month format b. this must work for a table where TransactionDate is a different field with text data.
Feb 11 '12
69 8586
12,516 Recognized Expert Moderator MVP
I didn't think that it would be a large difference. The problem is that the 150,000 records are joined to itself. So it's practically comparing 150000 ^ 2. And that's a lot of records to process. That would be the largest contributing factor to the speed of and would far outweigh the overhead of VBA.

However, this may be because of a lack of proper indexes. Perhaps an index on the client and month would improve the speed of the SQL.

Now, if we were talking about SQL Server 2005+, it has a row numbering function that would significantly speed up the processing because you don't have to join the table to itself. I'm talking seconds rather than minutes.
Feb 14 '12 #51
8,834 Recognized Expert Expert
I also do not think that
testing the performance on 100 rows and then multiplying the result by 1500 (total of 150000)
is a reliable Benchmark. The Testing needs to be performed on the entire Data set of 150,000 Records. Comments?
Feb 14 '12 #52
12,516 Recognized Expert Moderator MVP
Hard to say, because the numbers seem to be in line with what I got. I tested the SQL on 170,000 rows and it took a little under 6 minutes.

A full test couldn't hurt, if it's only 6-7 minutes it would be beneficial to just do a full test.
Feb 14 '12 #53
12,516 Recognized Expert Moderator MVP
I whipped this up. It's a simplified replication of the ROW_NUMBER() function in SQL Server. It should be many magnitudes faster.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Dim row As Double
  5. Dim strPartition As String
  7. Public Function RowNum(partition As String) As Double
  8.     If partition <> strPartition Then
  9.         row = 0
  10.         strPartition = partition
  11.     End If
  13.     row = row + 1
  14.     RowNum = row
  15. End Function
Expand|Select|Wrap|Line Numbers
  1. SELECT partitionField, 
  2.    orderField,
  3.    otherField
  4. FROM someTable
  5. WHERE RowNumber(partitionField) < 4
  6. ORDER BY partitionField,
  7.    orderField
PS: It's not as quick as the SQL Server version but it takes half the time as the previous solutions. I ran it on 170000 records and it took a little under 3 minutes.
Feb 14 '12 #54
32,584 Recognized Expert Moderator MVP
Then I get a Cannot group on fields selected with '*' (qTH1). error.
OK. That means Jet SQL needs it spelled out long-hand. Unfortunate, as I was trying to minimise unnecessary verbage :-(

Expand|Select|Wrap|Line Numbers
  1. SELECT   qTH1.ClientID
  2.        , qTH1.TransactionNumber
  3.        , qTH1.MonthOrder
  4. FROM     [qryTransactionHebrew] AS [qTH1]
  5.          INNER JOIN
  6.          [qryTransactionHebrew] AS [qTH2]
  7.   ON     (qTH1.ClientID = qTH2.ClientID)
  8.  AND     (qTH1.MonthOrder >= qTH2.MonthOrder)
  9. GROUP BY qTH1.ClientID
  10.        , qTH1.TransactionNumber
  11.        , qTH1.MonthOrder
  12. HAVING   Count(*) < 4
  13. ORDER BY qTH1.ClientID ASC
  14.        , qTH1.MonthOrder DESC
Essentially the same as Rabbit's version but with more meaningful names (Well, you couldn't expect him to read my mind now, could you).
Feb 15 '12 #55
32,584 Recognized Expert Moderator MVP
On the performance issue, it would be interesting (think critical) to know whether or not [ClientID] is indexed. I would normally expect the SQL only version to be significantly faster, all else being equal. I didn't study the VBA suggested closely enough to determine if it was implementing the same logic as the SQL. Nor can I honestly say what I mean by significantly faster. A little more than was shown to be sure, but not necessarily much more.

I believe the killer issues are :
  1. The number of records involved.
  2. The fact that the data of each record needs to be run through [qryTransactionH ebrew] before it's sorted.

PS. Did the OP ever comment on the problem related to months of different years, raised in post #19?
Feb 15 '12 #56
46 New Member
I'm not sure what your pseudo code means, can you please elaborate?


Thank you very much, it works well.
Feb 15 '12 #57
12,516 Recognized Expert Moderator MVP
It wasn't pseudo code. Well, the SQL query won't run with your table set up but it's syntactically correct. It basically numbers the rows breaking by the grouping field and then uses that to filter the rows. It should run at least twice as fast as the other methods.

NeoPa, I don't think that they did.
Feb 15 '12 #58
46 New Member

Regarding the problem related to months of different years, raised in post #19, there is another problem, the Hebrew calendar has 6 leap years (13 months instead of 12) in a 19 year cycle, so the order will not be accurate.
but both of those issues can be solved quite simply albeit not in a smart or professional manner, in tblHebrewMonthO rder I can add the values for the coming years (expected lifetime of the mdb).
Feb 15 '12 #59
46 New Member

What table setup do I need for your new query to work?
Feb 15 '12 #60

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Matt | last post by:
I have a project where I need to have navigation buttons. Now I got them working but I found a problem that occurs when there are records deleted from the DB. The DB I am accessing has an ID column that is the primary key and uses AutoNumber. My program also allows the user to delete records. My problem occurs when someone deletes a record other than the first or last record in the DB. The way my Next navigation button works is that it...
by: Lyn | last post by:
I have a form set to Single Form mode with which I can cycle through the records in a table via Next and Previous buttons. To avoid users pressing the Previous button on the first record and the Next button on the last record, I would like to disable one or both buttons when the first and/or last record is displayed. I am not sure how to do this when the RecordSource is simply the table. I know that if the record source were a...
by: booksnore | last post by:
I am reading some very large files greater than 10 GB. Some of the files (not all) contain a header and footer record identified by "***" in the first three characters of the record. I need to delete the header or footer record before reading the file into a database. Whats the best way to do this in C#? Any help appreciated. Joe *** Sent via Developersdex http://www.developersdex.com ***
by: schapopa | last post by:
Hi, I want to create query where I could group records by quarters, and get the last record in each group. e.g Create Table MyTable ( Value , date )
by: Tomas | last post by:
I'm creating MS Access database and I need to have in query an average of 3 last records. How to do it? Maybe here is some function or sql expresion? Thanks
by: perryche | last post by:
I don't know where to begin search for this... I want to add a record, but still able to see the page, without bringing it to a new page and have to scroll back to see other records. I hope I explain myself here. Or, is there a way to add record being on the top of other records and not go to the bottom? Thanks. Perry
by: jith87 | last post by:
hi, i am importing a text file into oracle database using sql loader.i need to ommit the first and last records of the text file while importing... can anybody help???? this is my text file... A: Removable 0.0% C: Fixed NTFS 39.06 GB 26.10 GB 66.8% D: Fixed NTFS 43.95 GB 30.44 GB 69.3% E:...
by: PeteM | last post by:
Using SQL Server 2005 I have on a number of occasions, succesfully used the following code to create tables with the last dated results or observations for each Patient. However I am now being asked to do the same in Access 2003 and all the variations I can think of return the error message “At most one record can be returned by this subquery”. Do I have to give up with Access? SELECT Patient, (SELECT TOP 1 t1.LabResult1 FROM ...
by: Greg (codepug | last post by:
I want to display the last 22 records in my continuous form. I have writen the following code and it works, but was wondering if there were any better suggestions for accomplishing this. My code scrolls the screen as it goes to bottom and than scrolls out the 22 recs. My code: Private Sub GoToBottom()
by: asdasd10 | last post by:
i tired do it alone but its gives me an eror like that: "r object cant move back" here is my code : <% path = Server.mappath("../db/ServerData.mdb") set con = Server.createobject("ADODB.Connection") con.open "driver={Microsoft Access Driver (*.mdb)};DBQ="&path set r = con.execute("select * from News") Dim i
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.