How do I only return the top 5 records

[hershamboy]

I want to run a query which sums up instances over a period and only return the top 5 occurances,

This is the code that shows all results.


select po.vendor_cd,supplier_nm,end_dt,
sum(1) as created_qty


from
ENG_NOTIFICATIONS nots left outer join eng_purchase_orders po on nots.purchase_ord_id = po.purchase_ord_id and po.curr_record_ind = 'y'
left join eng_suppliers sup on po.vendor_cd = sup.vendor_cd and sup.curr_record_ind = 'y'
inner join ref_mmco_date on nots.created_dt between start_dt and end_dt and date_typ = 'rm'
and end_dt between date-190 and date
where
nots.notification_typ = 'zr'
and sup.vendor_cd is not null

group by 1,2,3
order by 4 desc

 

[IanWaterman]

Think its just

select top(5) po.vendor_cd,supplier_nm,end_dt,
sum(1) as created_qty
.....


Ian

 

[markros]

What is your SQL Server version? Do you want to return top 5 per each group?

It is piece of cake in SQL Server 2005 or up and a similar question was discussed recently (few months ago) on another forum, I can search quickly for older versions.

 

[hershamboy]

Using top 5 worked perfectly.


Thanks for your help