All Forums UDA
Latha Nathan 1 post Joined 11/06
22 Nov 2006
Teradata - RANK function

For the below give query...sel A.BusinessGroup ,A.TRN_TYPE TRAINTYPE ,A.MAX_TRN_LGTH MAX_LGTH ,A.TRN_LGht_FT ACTUAL_LGTH ,A.MAX_TRN_LGTH - A.TRN_LGht_FT AVAIL_LGTH ,A.max_trn_wgt MAX_TONN ,a.grs_tons ACTUAL_TONN ,a.max_trn_wgt - a.grs_tons AVAIL_TONN,FROM cwx_dpr0.vcvt_od_trn AINNER JOIN ( SELECT CVT_SHPMT_TCD ,ORIG_CITY_333 ,ORIG_ST ,DEST_CITY_333 ,DEST_ST ,TRN_TYPE ,WEEK_DAY_ID ,MIN(max_trn_lgth - trn_lght_ft ) TRN_LGTH_AVAIL ,min(max_trn_wgt - grs_tons) TRN_WGT_AVAIL FROM cwx_dpr0.vcvt_od_trn where ORIG_CITY_333 = 'LOSANGELE' AND DEST_CITY_333 = 'CHICAGO' GROUP BY 1,2,3,4,5,6,7 ) B ON A.CVT_SHPMT_TCD = B.CVT_SHPMT_TCD AND A.ORIG_CITY_333 = B.ORIG_CITY_333 AND A.ORIG_ST = B.ORIG_ST AND A.DEST_CITY_333 = B.DEST_CITY_333 AND A.DEST_ST = B.DEST_ST AND A.TRN_TYPE = B.TRN_TYPE AND Avail_Lgth = B.TRN_LGTH_AVAIL and AVAIL_TONN = b.TRN_WGT_AVAIL where A.ORIG_CITY_333 = 'LOSANGELE' AND A.DEST_CITY_333 = 'CHICAGO' group by BUSGRP,TRAINTYPE, MAX_LGTH, ACTUAL_LGTH, AVAIL_LGTH, MAX_TONN, ACTUAL_TONN, AVAIL_TONN order by 1,2,8 QUALIFY RANK(AVAIL_TONN ASC) = 1;The resultset is as follows:CARLOAD H 8000 6881 1119 12000 10439 1561 CARLOAD H 8000 7113 887 12000 10291 1709 CARLOAD H 8000 7031 969 12000 9876 2124 CARLOAD H 8000 6414 1586 12000 9642 2358 CARLOAD H 8000 6879 1121 12000 9604 2396 CARLOAD M 6000 6554 -554 5000 6118 -1118 CARLOAD M 7000 7725 -725 10000 10201 -201 CARLOAD M 6000 4252 1748 5000 2511 2489 CARLOAD M 6000 3706 2294 5000 2453 2547 CARLOAD M 6000 3428 2572 5000 2251 2749 CARLOAD M 7000 5787 1213 10000 6000 4000 INTERMODAL Q 7000 7346 -346 5000 6323 -323 INTERMODAL Q 7000 7346 -346 5000 6323 -1323 INTERMODAL Q 8000 7676 324 8000 6669 1331 INTERMODAL Q 8000 7676 324 8000 6669 1331 INTERMODAL Q 8000 7646 354 8000 6470 1530 INTERMODAL Q 8000 7646 354 8000 6470 1530 INTERMODAL Q 8000 7235 765 8000 6244 1756 INTERMODAL Q 8000 7235 765 8000 6244 1756 But then I just want only those rows which has the least value for the last column grouping the data based on cols 1 and 2 ie., I actually wanted only the following 3 rows:CARLOAD H 8000 6881 1119 12000 10439 1561 CARLOAD M 6000 6554 -554 5000 6118 -1118 INTERMODAL Q 7000 7346 -346 5000 6323 -323 But even after applying a QUALIFY on the RANK function, i amnt to get the required data. Can you please help me acheive this?

dnoeth 3172 posts Joined 11/04
22 Nov 2006

Hi Latha,you're doing the RANK using old Teradata style, which is not recommended anymore.It's ranking for each combination within Group By.Simply replace it with SQL:1999 style:QUALIFY RANK() over (partition by BUSGRP,TRAINTYPE order by AVAIL_TONN ASC) = 1Dieter

Dieter

You must sign in to leave a comment.