Teradata - RANK function

UDA
Highlighted

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 A
INNER 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?
1 REPLY
Senior Supporter

Re: Teradata - RANK function

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) = 1

Dieter