CODEWITHSUNDEEP
teradatarankrow-number
user3319338
user3319338

Reputation: 33

How use Qualify row_number in teradata

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!

Upvotes: 0

Views: 8188

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 0

Clark Perucho
Clark Perucho

Reputation: 466

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;

Upvotes: 1

Related Questions