Toad World® Forums

Top 5 by Product ID


#1

Is it possible to take the Top 5 sales for each product over a period of time without filtering on each product? I have a large number of “products” and i need to find their top 5 “transactions” over the last year. I know how to do this in VBA/Access but I was wondering if it was possible in TDA


#2

In the Query Builder there is a TopN feature. See screenshot.

Debbie
TopN.png


#3

I may have written my question incorrectly, given the example you provided I would be looking for the top 5 rows for each “region id”. Assuming there are 10 regions is there a way to cycle through each of these regions and provide the top 5 rows (some kind of transaction data would be present for each region) for each without filtering by each individual region.

Message was edited by: Dave - Stt


#4

I actually found what I was looking for…

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) “Rank”
FROM employees;

This will rank each salary within each department (this example is from the online reference provided under code snippet rank_analytic)


#5

Woops. I forgot to answer you post.

Good solution.