How to get min and max value in a single olap query using ROW_NUMBER in teradata

Database
Enthusiast

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.


5 REPLIES
Junior Contributor

Re: How to get min and max value in a single olap query using ROW_NUMBER in teradata

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)
Enthusiast

Re: How to get min and max value in a single olap query using ROW_NUMBER in teradata

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???

Junior Contributor

Re: How to get min and max value in a single olap query using ROW_NUMBER in teradata

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.

Re: How to get min and max value in a single olap query using ROW_NUMBER in teradata

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

Enthusiast

Re: How to get min and max value in a single olap query using ROW_NUMBER in teradata

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