Top 100

I have a teradata table that I need to get:
top 100 revenue -----    SUM(temp.SHP_FGT_CHRG_AMT) as Revenue
The query is below and works, but how can I just get that group?
Thanks if you can help!!

SELECT 
temp.natl_acct_nbr as "National Acct",
temp.cust_nm as "Customer Name",
SUM(temp.SHP_FGT_CHRG_AMT) as Revenue,
COUNT(temp.natl_acct_nbr) as "# shipments"

FROM ( 
Select 
a.natl_acct_nbr,
a.cust_nm,
'NO ZIP'  as szip,
'NO ZIP' as rzip,
'NO ZIP PAIR' as zippair,
shp_cntry,
recp_cntry_cd,
v.last_known_srt_loc_scn_loc_cd,
shp_cntry||' - '||recp_cntry_cd as ilane,
SVC_BAS_CD,
SHP_FGT_CHRG_AMT

From  UI_ISH_PROD_DB.billed_intl_express i
Inner Join UI_ISH_PROD_DB.billed_intl_misc im 
On i.inv_unique_id= im.inv_unique_id
And    i.AB_SEQ_NBR = im.ab_seq_nbr

/*Left Outer Join ui_ish_prod_db.billed_intl_misc_resn_cd ic 
On im.misc_rsn_cd = ic.misc_resn_cd*/

 Inner Join SCAN_PROD_RESTRICTED_VIEW_DB.exprs_vol_vsbty_esqi_enhmt v 
ON i.SHP_TRK_NBR = v.shp_trk_nbr

Inner Join (sel a.natl_acct_nbr,
				a.cust_nbr,
				a.cust_nm
				from UI_ISH_PROD_DB.customer a
 				group by 1,2,3
				) a
ON  i.payr_cust_nbr  = a.cust_nbr

Where  a.cust_nbr = i.payr_cust_nbr
AND  inv_month between 201601 and 201606
            --And    e.ab_inv_typ_ind not in ('0')
---And im.misc_rsn_cd IN ('273', '357')
AND v.last_known_srt_loc_scn_loc_cd IN ('CANH', 'CGNH','INDH')

 UNION ALL

 Select 
x.natl_acct_nbr,
x.cust_nm,
x.szip,
x.rzip,
x. zippair,
'US' As shp_cntry,
'US' As recp_cntry_cd,
x.last_known_srt_loc_scn_loc_cd,
'US - US'As ilane,
x.SVC_BAS_CD,
x.shp_fgt_chrg_amt

from
(
sel e.SHP_TRK_NBR,
e.INV_UNIQUE_ID,
e.AB_SEQ_NBR,
e.payr_cust_nbr,
a.natl_acct_nbr,
a.cust_nm,
v.last_known_srt_loc_scn_loc_cd,
e.szip,
e.rzip,
e.zippair,
e.SVC_BAS_CD,
e.shp_fgt_chrg_amt

From ( sel e.SHP_TRK_NBR,
				e.INV_UNIQUE_ID,
				e.AB_SEQ_NBR,
				e.payr_cust_nbr,
				substr(e.ZONE_ORIG_PSTL_CD,1,5) as szip,
				substr(e.RECP_PSTL_CD,1,5) as rzip,
				substr(e.ZONE_ORIG_PSTL_CD,1,5)||'- '||substr(e.RECP_PSTL_CD,1,5) AS zippair,
				e.shp_fgt_chrg_amt,
				e.SVC_BAS_CD
			From UI_ISH_PROD_DB.billed_dom_express e 
			where e.inv_dt_yyyymm between 201601 and 201606
			And    e.ab_inv_typ_ind not in ('0')
			group by 1,2,3,4,5,6,7,8,9
			) e
Inner Join UI_ISH_PROD_DB.dom_inv_misc dm 
On e.INV_UNIQUE_ID = dm.inv_unique_id
---And e.AB_SEQ_NBR = dm.ab_seq_nbr
--And dm.misc_resn_cd = '050'

/*Left Outer Join ui_ish_prod_db.miscellaneous_reason_code rc 
On dm.misc_resn_cd = rc.misc_resn_cd*/

Inner Join (sel v.shp_trk_nbr,
						v.last_known_srt_loc_scn_loc_cd
					from SCAN_PROD_RESTRICTED_VIEW_DB.exprs_vol_vsbty_esqi_enhmt v 
					where v.last_known_srt_loc_scn_loc_cd = 'INDH'
					group by 1,2
					) v
ON e.SHP_TRK_NBR = v.shp_trk_nbr

Inner Join (sel a.natl_acct_nbr,
				a.cust_nbr,
				a.cust_nm
				from UI_ISH_PROD_DB.customer a
 				group by 1,2,3
				) a
ON  e.payr_cust_nbr  = a.cust_nbr

) x
) temp     
Sample 100  
 
group by 3,1,2

Select allOpen in new window