How use Qualify row_number in teradata

0
votes

how are you?

I never used qualify row_number()/rank() and I have some questions.

I am trying this query in teradata:

select sit_site_id
     , count(distinct shp_shipment_id) 
from WHOWNER.BT_SHP_SHIPMENTS
group by sit_site_id
QUALIFY RANK() OVER (PARTITION BY sit_site_id 
                     ORDER BY count(distinct shp_shipment_id) ) = 3

But the result is: 'No data available in table'.

I want to get the first 3 sit_site_id values with more shp_shipment_id.

Where is my error?

Thanks!

2
teradatarankrow-number
The GROUP BY returns a single row per site_id and then you try to get the 3rd row, of course that returns no rows. You probably want something like QUALIFY RANK() OVER ( ORDER BY count(distinct shp_shipment_id) ) <= 3 which returns the three highest counts. - dnoeth
Do you need top 3 site_id per every shipment_id? Kindly share sample data and desired output. - zarruq

2 Answers

1
votes

I want to get the first 3 sit_site_id values with more shp_shipment_id

If so, then you do not need to use RANK() / ROW_NUMBER(). Instead, you can just use TOP. Your query should be like below:

select TOP 3 sit_site_id
     , count(distinct shp_shipment_id) shp_shipment_id_cnt
from WHOWNER.BT_SHP_SHIPMENTS
group by sit_site_id
order by shp_shipment_id_cnt DESC;
0
votes

If you want to use qualify for this, then you can do:

select sit_site_id, count(distinct shp_shipment_id) 
from WHOWNER.BT_SHP_SHIPMENTS
group by sit_site_id
qualify row_number() over (order by count(distinct shp_shipment_id) desc) <= 3;

However, I would recommend top as in @Clark's answer.