asked on

top 100 or rank 100 teradata

Hi, I need to get top 100 from below:
the top 100 count(i.payr_nat_acct_nbr),

Select 
i.payr_nat_acct_nbr,
count(i.payr_nat_acct_nbr),
v.last_known_srt_loc_scn_loc_cd


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)
            Where  

            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')
            
ORDER BY i.payr_nat_acct_nbr, v.last_known_srt_loc_scn_loc_cd
Group by i.payr_nat_acct_nbr, v.last_known_srt_loc_scn_loc_c

Open in new window

Sean Stuber

teradata supports row_number and rank

try those

SELECT x.*, ROW_NUMBER() OVER (ORDER BY cnt DESC) as rn
FROM ( SELECT i.payr_nat_acct_nbr, COUNT(i.payr_nat_acct_nbr) cnt, v.last_known_srt_loc_scn_loc_cd
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)
WHERE 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')
GROUP BY i.payr_nat_acct_nbr, v.last_known_srt_loc_scn_loc_c) as x
WHERE rn <= 100
ORDER BY payr_nat_acct_nbr, last_known_srt_loc_scn_loc_cd

Euro5

ASKER

Have this error

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

Did you try this.

Select TOP 100 
i.payr_nat_acct_nbr,
count(i.payr_nat_acct_nbr) cnt,
v.last_known_srt_loc_scn_loc_cd
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)
            Where  

            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')
Group by i.payr_nat_acct_nbr, v.last_known_srt_loc_scn_loc_c
ORDER BY cnt DESC

Open in new window