qualify rank() over (partition.....question

Database
Highlighted

qualify rank() over (partition.....question

Hi everyone....

This query returns the top(1) commodity and count for each of the 10 customers. This query works fine and returns the desired results except for the additional row where the cnt value is the same. Of the 2 cnt = 22 rows I would be happy with taking the first record based on commodity_desc alphabetically (CHEMICALS in this case).

Results:
cust_acct_nbr commodity_desc cnt
11083 GIFTWARE, NOVELTIES 113
2223626 CHEMICALS 61
3912675 COMPUTERS 96
5948090 ELECTRONIC PARTS 42
601163 CHEMICALS 101
614014 COMPUTERS 59
6287794 ELECTRICAL AUDIO VIDEO 24
663609 HOUSEWARES 41
7580149 CHEMICALS 22
7580149 TOILETRIES 22

8089459 FURNITURE 18

Query:
sel m.cust_acct_nbr cust_acct_nbr, com.commodity_desc, count(m.claim_cycle_sequence_nbr) cnt
from claim_prod_view_db.claim_detail m, claim_prod_view_db.claim_commodity com,
ui_results_db.tl434409_dmg_denied_shprs shprs ---table contains the 10 customer values---
where m.commodity_cd = com.commodity_cd
and m.cust_acct_nbr = shprs.fxg_shpr
and com.company_cd = 'FDEG'
AND m.sqi_intl_dom_cd = 'D'
AND m.sqi_count_flg = 'Y'
AND m.ship_dup_flg = 'N'
AND m.sqi_loss_damage_flg IN ('d', 'D')
AND m.sqi_paid_denied_flg ='d'
and m.company_cd = 'FDEG'
AND m.claim_close_dt >= '2009-06-01'
AND m.claim_close_dt < '2009-07-01'
group by 1,2
qualify rank() over (partition by cust_acct_nbr order by cnt desc) = 1

I'm new to Teradata (coming from Oracle) and new to the forum and appreciate any help anyone can give. Thanks.
24 REPLIES
Supporter

Re: qualify rank() over (partition.....question

You do exactly the same, you would do in Oracle :-)

Replace RANK with ROW_NUMBER:
qualify row_number() over (partition by cust_acct_nbr order by cnt desc, commodity_desc) = 1


Dieter

Re: qualify rank() over (partition.....question

Thanks Dieter...guess I should stop trying to over think these things.
med
Fan

Re: qualify rank() over (partition.....question


hello, i need some help please,



I have a dataset similar to the following:



Patient_ID Unit       Start_Time        End_time

1234             3FE       3/2/09 12:00     3/5/09 13:00

1234             3FE       5/5/09 13:00      5/5/09 14:00

1234             3FE       5/5/09 14:00      5/7/09 18:00

1234             ICU       5/7/09 18:00      5/16/09 5:00

1234             3FE        5/16/09 5:00      5/23/09 7:00

1234             3FE       5/23/09 7:00      5/25/09 3:00

1234             3FE        5/25/09 3:00      5/28/09 8:00



 



The result set should look like the following:



 



Patient_ID Unit       Start_Time        End_time

1234             3FE       3/2/09 13:00      5/7/09 18:00

1234             ICU       5/7/09 18:00      5/16/09 5:00

1234             3FE        5/16/09 5:00      5/28/09 8:00



 



 



Any suggestions would be appreciated.



 





 


Supporter

Re: qualify rank() over (partition.....question


The expected result doesn't match your example data, the first end_time should probably be 5/5/09 13:00.



There are several ways using OLAP functions, the most efficient is probably:



SELECT Patient_ID, Unit, Start_Time,
COALESCE(MIN(prev_end_time)
OVER (PARTITION BY patient_id, Unit
ORDER BY start_time
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
,max_end_time) AS End_time
FROM
(
SELECT Patient_ID, Unit, Start_Time,
MAX(End_time)
OVER (PARTITION BY patient_id, Unit
ORDER BY start_time
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end_time,
MAX(End_time)
OVER (PARTITION BY patient_id, Unit) AS max_end_time
FROM dropme
-- find the first row after a gap
QUALIFY start_time > prev_end_time OR prev_end_time IS NULL
) AS dt


If you're on TD13.10 one of the new functions to normalize periods will be faster:



WITH cte(Patient_ID, Unit, pd) AS
(
SELECT
Patient_ID,
Unit,
PERIOD(CAST(Start_Time AS TIMESTAMP(6)), CAST(End_Time AS TIMESTAMP (6))) AS pd
FROM dropme
)
SELECT
Patient_ID,
Unit,
BEGIN(pd) AS Start_Time,
END(pd) AS End_Time
FROM TABLE
(
TD_SYSFNLIB.TD_NORMALIZE_OVERLAP_MEET(NEW VARIANT_TYPE(cte.Patient_ID, cte.Unit),
cte.pd)
RETURNS (Patient_ID INT, Unit CHAR(3), pd PERIOD(TIMESTAMP(0)))
HASH BY cte.Patient_ID, cte.Unit
LOCAL ORDER BY cte.Patient_ID, cte.Unit, cte.pd
)
AS dt (Patient_ID, Unit, pd);


This query might return the count of normalized rows, too.



Dieter


med
Fan

Re: qualify rank() over (partition.....question


thanks Dieter thats great !!


Fan

Re: qualify rank() over (partition.....question

what is the replacement of "qualify rank()" which is in the teradata to informix query.?


Fan

Re: qualify rank() over (partition.....question

Hi Dieter , 


 


I have the similar requirement with slight change , need your Help in that -


 


My dataset is as below -


Company ID   Effective Date   Status Codes


C1                    1st March              JS


C1                     2nd March             AB


C1                     3rd March              ZB - previous row


C1                       4th March            JS


C1                     5th March              JS - current row


 


I want my resultset to return only current and preiosu record where there is change in the status , like  below


Company ID                     Current Status                Preious Status


C1                                        JS                                    ZB


can you please help me in this.


 


Thanks


Kanch


Supporter

Re: qualify rank() over (partition.....question

Hi Kanch,


i don't get your requirement, you should post some more data and more detailed explanation, why you need which output.


Dieter


Fan

Re: qualify rank() over (partition.....question

Hi Dieter ,


I am having a company record in table having different status stored  as below -


Comapny ID - company identifier


Effv Date - Date on which record is getting inserted to table


Staus Codes - Status codes linked to company identifier , which can be same or different thatn previous record.


Company ID   Effective Date   Status Codes

C1                    1st March              JS

C1                     2nd March             AB

C1                     3rd March              ZB 

C1                     4th March               JS

C1                     5th March              JS 


Here I want to return the company identifier along with


-it's currentstatus i.e. J S (5th March)


-it's previous status where there is a change i.e. Z B (3rd March).


Thanks for your Help!

 


Kanch