SQL Assistance needed

Tools

SQL Assistance needed

I am attempting to find the minimum run_date in a table where there are more than 10 run dates. In other words if there are 12 run dates in the table I want the date of the 11th run_date so I can delete all the records with run dates older than that date. This is the SQL I was attempting to get this data:


select min(a.run_dt), '1' as sequence,
CSUM(sequence,a.run_dt) seq
from (
select run_dt
from table_name
group by run_dt
) a
order by run_dt
having seq > 10
;


But, I am getting the following error:
5876: Ordered Analytical Functions not allowed in HAVING Clause.


Can someone recommend another way to obtain this data. The SELECT will eventually be changed to a DELETE

Thanks in Advance
3 REPLIES
SN
N/A

Re: SQL Assistance needed

As the error msg says....CSUM is an ord. analytical fn. and HAVING clause cannot be used with it.

try this in sequence....

1.create volatile table unique_run_dt as (
sel run_dt from mytable group by 1) with data on commit preserve rows;

2.create volatile table vt_run_dt as (
sel run_dt,rank() over (order by run_dt) as rank_dt
from unique_run_dt qualify (rank_dt)=11) with data on commit preserve rows;

3.del from mytable where run_dt < (sel run_dt from vt_run_dt);

thx
Teradata Employee

Re: SQL Assistance needed

Hello,

Can you please provide the example what exactly you want to do?

Regards,

Adeel

Re: SQL Assistance needed

we can go for rank function to get the same output.
please check the following query.

select run_dt, rank() over (order by run_dt)
From db.table
group by run_dt
qualify rank() over (order by run_dt) > 2;

Thanks & Regards
Hari Kumar