Last 3 records by group

[moishy101]

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.

[yarbrough40]

if you know which client IDs you want ahead of time. you could use:

SELECT TOP 3 * FROM MyTable
WHERE ClientID = 1
UNION ALL
SELECT TOP 3 * FROM MyTable
WHERE ClientID = 2
ORDER BY ClientID,TransactionDate

[moishy101]

I want the query to show all ClientId's (there are some 150000).

[ADezii]

Shouldn't the results for Client# 1 be:

ClientID TransactionID TransactionDate
    1       4001184      18/05/2011
    1       6511216      01/02/2012
    1       9849528      11/02/2012

[ADezii]

There is a relatively simple solution using VBA, but I'll see what the SQL Gang comes up with first.

[moishy101]

Shouldn't the results for Client# 1 be:

ClientID TransactionID TransactionDate
    1       4001184      18/05/2011
    1       6511216      01/02/2012
    1       9849528      11/02/2012

ADezii,

You are right, my mistake.

[NeoPa]

@Moishy101.
Unless and until you complete the question by explaining under which circumstances 'the last three' has meaning, we're left guessing what you mean. This is not a good way to proceed, so if you want meaningful help, then I suggest you clarify the question by explaining what you should have included in your original question.

[ADezii]

@moishy101:
The key, as I see it, is NOT to perform a Secondary Sort on the [TransactionDate] itself since it must remain Text, but on the Expression
CDate([TransactionDate]). The Records that you want returned would be the 'BOTTOM' 3 for each ID ASC with CDate([TransactionDate]) ASC.

[moishy101]

@NeoPa:
I'm sorry but I don't understand what you meant when you wrote: "explaining under which circumstances 'the last three' has meaning"
the table contains thousands of records and I need a report to show the last 3 records for each id. What else can I write to clarify matters?

[moishy101]

@ADezii:
Your suggestion may work in the sample table but as I wrote "this must work for a table where TransactionDate is a different field with text data" I meant that TransactionDate is replaced by TransactionMont h who's values are Jan, Feb, Mar, Apr etc.

[Mihail]

I am not sure I understand well your problem so forget me if this is not a help for you:
Create a query with all your fields and one more. Use the new one field to keep the numeric values for months. Something like this:

NewField:IIF(TD="Ian",1,IIF(TD="Feb",2,IIF(TD="Mar",3,IIF......

This way you can sort by months in this NewField and use what ADezii teach you.

[ADezii]

@moishy101:
Kindly Post some sample Data reflecting exactly the possible Values for [TransactionDate] and [TransactionMont h].

[NeoPa]

Moishy101:
I'm sorry but I don't understand what you meant when you wrote: "explaining under which circumstances 'the last three' has meaning"

The last (or last three) has a meaning that depends on the ordering of the items. So, for instance, the following data would have different 'last' records depending on whether it was ordered by [Date Arrived], [Date Left], or even [Name] :

[B][U]Name   [/U]  [[U]Date Arrived[/U]]  [[U]Date Left[/U]][/B]
Angus      1 Jan 2012    1 May 2011   Last by [Date Left]
Barnaby    1 Feb 2012    1 Apr 2011   Last by [Date Arrived]
Charles    1 Oct 2011    1 Mar 2011
David      1 Nov 2011    1 Feb 2011
Edward     1 Dec 2011    1 Jan 2011   Last by [Name]

PS. There is also the possibility of considering the last records that were entered into the table, but that is not illustrated easily, and if I understand correctly how Access manages its data, nor is it reliably determined without creating a field in the data to monitor it.

[NeoPa]

Moishy101:
Your suggestion may work in the sample table but as I wrote "this must work for a table where TransactionDate is a different field with text data"

Your original question was minimal. Very little explanation of what you want, or even what data you're working with. The only really useful part, from an experts point of view, was the example data. Now you explain that the example data is misleading. This makes trying to answer your question much more difficult than it should be.

You're responding well and sensibly to questions so I guess this is simply a mistake that shows limited understanding for what makes a good question, and some difficulty communicating. I'm sure that will come in time, but you will have to learn from this for future questions.

Moishy101:
I meant that TransactionDate is replaced by TransactionMont h who's values are Jan, Feb, Mar, Apr etc.

If your [TransactionDate] field is of type Date, then, and I'm guessing here as you haven't had a chance to reply yet to my question now (hopefully) you understand what I meant, if the sort order you are using is the transaction date, why are you replacing this with a string value reflecting only the month part? It seems a somewhat bizarre choice from the perspective of someone that only knows the little you've so far shared about the problem (IE. Me).

If your requirement is simply to select the three records with the maximum value of the [TransactionDate] field then you would surely be looking at using the TOP 3 predicate and sorting by [TransactionDate] DESC.

[ADezii]

@NeoPa:
What makes this confusing, at least to me, is that you actually need the TOP 3 Records by [ClientID] ASC, [TransactionDate] DESC, but only reversing the order of the 3.