How do I group, sort, then rank/number table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table fields PlyrID and TransDate with accompanying currency fields.
I want to select the last 6 transactions recorded for each UserID from which
I will apply aggregate functions and analysis. If I could add a
chronological count of records for each user I could select records with
=MaxCount-5. How woudl I go about this. From other postings it looks like
Click to expand...
I number all records based on UserID Asce, TransDate Desc and then add a
additonal qurey to select last 6 for each UserID but can it be done in one
SQL query?
 
I'm not sure exactly what you are doing here. A "Top" query will select the
Top X (whatever number - in this case 6) records from the output of the query
based on one sort field. So you could sort TransDate in descending order and
take the Top 6.

However if you need to do analysis ON that Top 6 then you would need two
queries - on to determine the Top 6 and one to apply any analysis. If you do
the analysis in the Top 6 query it will restrict your output and give you the
Top 6 of THAT restricted data set not of all the data.
 
Thanks. How do you perform a "Top" query? The only way I knew how to
accomplish this was to sort, add auto-number (called Rank) to table, then
select Rank <= X.

The complication here is that I need the last 6 transactions for each
UserID. I have 750,000 UserID's and 6,200,000 Transaction Records. I need
the average of last 6 for each UserID.

Can this be done with SQL query or other method?
 
In the query design view there is a box at the top that has "All" in it as a
default. This is the Top Values box. You can put any number in there and it
will do that as a Top query. You need to sort the field you want to choose
on either descending (top values) or ascending (bottom values).

You can also do it straight through SQL by typing "TOP {n}" after the word
SELECT as long as you "order by" the necessary field.

You can do a Top 6 query to identify the last 6 records for each user based
 
Back
Top