Top 5 grouping of material based on usage

[Matt]

I have a table that has material, customer name, region, and 24 month usage.
Primary keys are set on material, customer name, and region.

What I'm trying to do is bring back the top 5 records (if 5 records don't
exist, bring back as many records available, basically, <= top 5) for every
material based on highest 24 month usage regardless of customer and region.
Is there a way to do this? Thanks in advance for your help.

Example:

Material Customer Region 24 Mo Usage
ABC DELL Europe 10
ABC DELL ASIA 25
ABC SONY NA 30
ABC SONY NA 15
ABC HP ASIA 11
BCA HP EUROPE 13
BCA LENOVO NA 14
DDE LENOVO ASIA 12
DDE TOSHIBA ASIA 10
CDN APPLE NA 8
CDN APPLE NA 15

 

[MGFoster]

I have a table that has material, customer name, region, and 24 month usage.
Primary keys are set on material, customer name, and region.

What I'm trying to do is bring back the top 5 records (if 5 records don't
exist, bring back as many records available, basically, <= top 5) for every
material based on highest 24 month usage regardless of customer and region.
Is there a way to do this? Thanks in advance for your help.

Example:

Material Customer Region 24 Mo Usage
ABC DELL Europe 10
ABC DELL ASIA 25
ABC SONY NA 30
ABC SONY NA 15
ABC HP ASIA 11
BCA HP EUROPE 13
BCA LENOVO NA 14
DDE LENOVO ASIA 12
DDE TOSHIBA ASIA 10
CDN APPLE NA 8
CDN APPLE NA 15

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

** Change "table_name" to your table's name **

SELECT TOP 5 Material, Customer, Region, [24 Mo Usage]
FROM table_name
ORDER BY [24 Mo Usage] DESC

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmVtIYechKqOuFEgEQIz+QCfT8UPkf0roRNEXznLEdzU8VgNuW4An2tv
j+IKls+nt48kZA5teWOrURXj
=dxSP
-----END PGP SIGNATURE-----

 

[Allen Browne]

To get the TOP 5 per group, use a subquery to get the primary key value of
the desired records.

Details and example in:
http://allenbrowne.com/subquery-01.html#TopN

 

[Matt]

Thanks Allen. Got it to to work with the subquery.