Top N of Group

[UltraSmooth]

I'm having some problems coming up with a "Top N Of Group" query. I've googled it as well as gone through this forum but cannot come up with anything that works. Here's my situation, hopefully someone might have some tips to point me in the right direction.

I have 3 tables needed in the query.

An item header table, SYINVHDR, which contains an item number and category information (what I want to group by).

An item detail table, SYINVDTL, which contains details such as colour,size,width, location and price/cost information.

A sales table, SYINVHIS, that contains a weekly sales summary record for each item by item/location.

I need to join the three tables as I need the category from syinvhdr (group by), syinvhdr joins to syinvdtl on item to get the details such as colour,size etc. and then this joins to syinvhis to get the sales for the item for a particular week.

What i would like to get is the top N selling items for each category for a specified date.

Anyone have any suggestions on how to go about his?

 

[Kozusnik]

Use a sub query.

select * from (
select fields from table
join...
join...
group by field(s)
) as t1
order by some-field desc
limit N

Mark

 

[UltraSmooth]

Thanks for responding Mark. I have tried implementing something like what you mentioned (how I interpreted it anyways) but am still not getting the correct results. Here's my query,

SELECT trim(substr(pm_element,4,3)) AS department
	, pm_str_var as dept_desc 
	, sales.*
FROM syparamt
LEFT JOIN (
	SELECT syinvhdr.ih_dept
		, syinvhdr.ih_style
		, syinvdtl.id_loctn
		, SUM(syinvhis.his_salretl) as retail
		, SUM(syinvhis.his_salcost) as cost
	FROM syinvhdr
	JOIN syinvdtl
	ON syinvhdr.ih_style=syinvdtl.id_style
	JOIN syinvhis
	ON syinvhis.his_key=CONCAT(RPAD(id_style,10,' '),RPAD(id_colour,5,' ' ),RPAD(id_sze,3,' '),RPAD(id_width,3,' '))
		AND syinvhis.his_loctn=syinvdtl.id_loctn
		AND syinvhis.his_period='W'
		AND syinvhis.his_tdate=76089
		AND syinvhis.his_type='IT'
		AND syinvhis.his_loctn='01'
	GROUP BY syinvhdr.ih_dept
		, syinvhdr.ih_style
		, syinvdtl.id_loctn
	ORDER BY retail DESC
	LIMIT 5
) AS sales
ON sales.ih_dept = TRIM(substr(pm_element,4,3))
WHERE pm_element LIKE 'DEP%'
ORDER BY department ASC;

The problem I'm having is the subquery that calculates the top 5 items does so for all items, not just items pertaining to the particular department record that is being joined from the syparamt table, resulting in just 5 records for the overall top 5 items. Essentially I need the subquery to process the top 5 items based on the current department record being read from the syparamt table, it would be the same as manually adding the following to the subquery,

WHERE syinvhdr.ih_dept='D1' (some department)

How would I get the subquery to work like this?

Thanks,
Rob