Sounds simple, right, but for many usage of ordered analytic function here is new... we always think about MAX, TOP etc... so here is a quick reference:
SELECT ID FROM TABLE
QUALIFY RANK() OVER (ORDER BY ID DESC) <=10;
Update:
My assumption here is that ID field I use here is unique. If it is not unique, rank() cannot be used, row_number() needs to be used instead.
(thanks for pointing this out, carlosal!!)
Also, TOP can be used along with ORDER BY clause and achieve the result, but in my opinion & experience, ordered analytic function is much faster for this task than TOP
SELECT TOP 10 ID FROM TABLE
ORDER BY ID DESC;
Meena:
ReplyDeleteThat's not correct, because RANK() can give more rows than the number specified in the query (there is no DENSE_RANK() in Teradata). You should use ROW_NUMBER() instead:
With a table like this:
SELECT * FROM MY_DB.PRUEBA01 ORDER BY 1;
*** Query completed. 9 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_N C_TXT
----------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO3
5 CINCO5
5 CINCO4
5 CINCO2
5 CINCO1
BTEQ -- Enter your SQL request or BTEQ command:
your query will retrieve more rows then the specified 'TOP' (3 in this example):
SELECT * FROM MY_DB.PRUEBA01 QUALIFY RANK() OVER (ORDER BY ID_N DESC) <=3;
*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_N C_TXT
----------- -------------------------
5 CINCO3
5 CINCO4
5 CINCO2
5 CINCO1
5 CINCO5
whereas ROW_NUMBER() will get the number of rows specified in the query (3):
BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM DW_USUARIO.PRUEBA01 QUALIFY ROW_NUMBER() OVER (ORDER BY ID_N DESC) <=3;
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_N C_TXT
----------- -------------------------
5 CINCO1
5 CINCO3
5 CINCO4
Cheers.
carlos.
hi meena.can u plz be detail for the above query.what is the difference if we use this query rather than max or top.
ReplyDeleteThanks, Carlos. Yes, I forgot to mention my ID field was unique, you are right, for cases when it is not. I have updated my post. Appreciate your inputs!
ReplyDelete@td,
ReplyDeleteMax will result in only one record, not highest 10
top will not give you highest either, they would provide random 10 records, unless you mention 'order by'
But yes, it is possible to achieve using top:
SELECT TOP 10 ID FROM TABLE
ORDER BY ID DESC;
Hi Meena, nice explanation..why are not posting new topics in teradata , can pls start posting..your informations on TD are very useful.
ReplyDeleteThanks Priya