All Forums Database
kumar_abhilash 15 posts Joined 04/14
30 Mar 2015
How to get min and max value in a single olap query using ROW_NUMBER in teradata

I have data like this.

ds_id                          Version    dwn_dt

------------------------------------------------------ ----

1697130072               1.3.9      2014-11-13 

1697130072               1.3.9      2014-11-13 

1697130072               1.3.9      2014-11-06 

1697130072               1.3.9      2014-11-06 

1697130072               1.3.9      2014-11-03 

1697130072               1.2.2      2014-09-09 

1697130072              1.2.2      2014-09-03 

1697130072               1.2.9      2014-09-03 

1697130072               1.2.9      2014-09-02 

9999999999480650   1.3.1       2014-10-27 

9999999999480650   1.3.1       2014-10-27 

9999999999480650   1.3.1       2014-10-27 

 

I want result like:-

 

ds_id                    Version(max) version(min)    dwn_dt(max)  dwn_dt(mmin)

------------------------------------------------------ --------------------------------------

1697130072             1.3.9              1.2.9           2014-11-13         2014-09-02

9999999999480650  1.3.1              1.3.1          2014-10-27         2014-10-27

 

Can anyone give me result like this using ROW_NUMBER function.

 

 

 

 

dnoeth 4628 posts Joined 11/04
31 Mar 2015

What's your TD release?
Assuming that a simple MIN/MAX/GROUP BY(ds_id) is not enough this will work with a single STATS step in TD14.10:

SELECT
   ds_id
  ,Version
  ,FIRST_VALUE(Version)  OVER (PARTITION BY ds_id ORDER BY dwn_dt)
  ,dwn_dt
  ,FIRST_VALUE(dwn_dt)   OVER (PARTITION BY ds_id ORDER BY dwn_dt)
FROM vt
QUALIFY ROW_NUMBER() OVER (PARTITION BY ds_id ORDER BY dwn_dt)
      = COUNT(*) OVER (PARTITION BY ds_id)

 

Dieter

kumar_abhilash 15 posts Joined 04/14
01 Apr 2015

Thanx dnoeth.This query is working fine for small volume of data but for larger volume(TD 14) i am getting this error.
 *** Failure 3610 Internal error: Please do not resubmit the last request.  SubCode, CrashCode:   0,  2628
                Statement# 1, Info =0 
 *** Total elapsed time was one minute and 20 seconds.

 

is it possible with row_number???

dnoeth 4628 posts Joined 11/04
02 Apr 2015

Is this a production system or a TD EXpress?
If it's production your DBA should open an incident with TD's customer support.
 
This will return the same result, but will need two STATS steps:

SELECT
   ds_id
  ,Version
  ,MIN(last_version)  OVER (PARTITION BY ds_id)
  ,dwn_dt
  ,MIN(last_dwn_dt)  OVER (PARTITION BY ds_id)
FROM
 (
   SELECT
      ds_id
     ,Version
     ,dwn_dt
     ,ROW_NUMBER() OVER (PARTITION BY ds_id ORDER BY dwn_dt) AS rn
     ,CASE 
         WHEN rn = COUNT(*) OVER (PARTITION BY ds_id)
         THEN Version
      END AS last_version
     ,CASE 
         WHEN rn = COUNT(*) OVER (PARTITION BY ds_id)
         THEN dwn_dt
      END AS last_dwn_dt
   FROM tab
 ) AS dt
QUALIFY rn = 1

Btw, you talk about TD14, but FIRST_VALUE is supported only in TD14.10.

Dieter

tmo-user 1 post Joined 06/15
23 Jun 2015

Have a below table,

customer ID, code, start_date, end_date
124343, DCW, 2015-07-06, 2016-08-03
235432, ABC, 2015-04-26, NULL
235432, ABC, 2015-04-26, 2015-06-20
3242342, ABC, 2015-08-02, 2015-07-28
2332434, DCW, 2015-02-09, 2015-06-23
2332434, DCW, 2015-06-23, NULL

What to use recurcive query to get output for unique code for each customerID,
1. when customer ID has more than 1 record in table with value in end_date and null in end_date then only records with null end_date. 
2. when customer ID has more than 1 record with start_date, end_date and start_date equal to end_date for the first record then only record with null end_date or future date than today's date. 
3. end_date more than today's date
Required Output

customer ID, code, start_date, end_date
124343, DCW, 2015-07-06, 2016-08-03
235432, ABC, 2015-04-26, null
3242342, ABC, 2015-08-02, 2015-07-28
2332434, DCW, 2015-06-23, null

gbansal 18 posts Joined 02/11
26 Jun 2015
SELECT * FROM test_olap2  
QUALIFY RANK () OVER (PARTITION BY customerID ORDER BY COALESCE (end_date,CURRENT_DATE +365) DESC )  =1
ORDER BY customerID  ;

Try using above query
let me know if it gives you what you want

You must sign in to leave a comment.