Equivalent of KEEP (DENSE_RANK FIRST ORDER BY )

Database
New Member

Equivalent of KEEP (DENSE_RANK FIRST ORDER BY )

Hi 

 

Can I know what is the equivalent of the below from oracle in Teradata

 

MIN(ID) KEEP (DENSE_RANK FIRST ORDER BY INSTR('12',COLUMN)

3 REPLIES
Teradata Employee

Re: Equivalent of KEEP (DENSE_RANK FIRST ORDER BY )

Your order-by phrase doesn't mean much to me, but instr() works the same way in Teradata as in Oracle.  As for Dense_Rank, that is a windowing aggregate in Teradata, so you could:

Select Min(ID)

Qualify Dense_Rank() over( order by ... ) = 1

 

Search Teradata Docs on Dense_Rank for more information.  You can also download entire SQL manuals at info.teradata.com.

Teradata Employee

Re: Equivalent of KEEP (DENSE_RANK FIRST ORDER BY )

FIRST / LAST being the Oracle aggregate functions (not keep, this is just a syntax element),you should publish a bit more of your query.

The MIN is there to handle if there are multiple candidate from the DENSE_RANK ORDER sort, this is confusing when novice to the syntax.

 

So the Qualify should look like this :

qualify row_number() over(order by instr('12', column) asc, id asc) = 1;
Junior Contributor

Re: Equivalent of KEEP (DENSE_RANK FIRST ORDER BY )

This is an aggregate function, without seeing the full Select list/Group by it's hard to tell how to rewrite.

Usually based on ROW_NUMBER or FIRST_VALUE...