tag:blogger.com,1999:blog-1556110042987012571.post4158384991168424617..comments2022-12-04T06:09:53.389-05:00Comments on Teradata Tips: highest 10 idsMeena Athmanathanhttp://www.blogger.com/profile/15342471362722574934noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-1556110042987012571.post-40936239286896615042012-07-09T23:44:05.298-04:002012-07-09T23:44:05.298-04:00Hi Meena, nice explanation..why are not posting ne...Hi Meena, nice explanation..why are not posting new topics in teradata , can pls start posting..your informations on TD are very useful.<br /><br />Thanks PriyaUnknownhttps://www.blogger.com/profile/14061610817445567640noreply@blogger.comtag:blogger.com,1999:blog-1556110042987012571.post-62303726293295513292011-12-21T10:35:47.448-05:002011-12-21T10:35:47.448-05:00@td,
Max will result in only one record, not highe...@td,<br />Max will result in only one record, not highest 10<br />top will not give you highest either, they would provide random 10 records, unless you mention 'order by'<br />But yes, it is possible to achieve using top:<br /><br />SELECT TOP 10 ID FROM TABLE<br />ORDER BY ID DESC;Meena Athmanathanhttps://www.blogger.com/profile/15342471362722574934noreply@blogger.comtag:blogger.com,1999:blog-1556110042987012571.post-70153917394057413642011-12-21T10:24:27.152-05:002011-12-21T10:24:27.152-05:00Thanks, Carlos. Yes, I forgot to mention my ID fie...Thanks, 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!Meena Athmanathanhttps://www.blogger.com/profile/15342471362722574934noreply@blogger.comtag:blogger.com,1999:blog-1556110042987012571.post-2207150527258827842011-11-12T04:08:28.906-05:002011-11-12T04:08:28.906-05:00hi meena.can u plz be detail for the above query.w...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.tdhttps://www.blogger.com/profile/17269607066716726207noreply@blogger.comtag:blogger.com,1999:blog-1556110042987012571.post-22291530413519211132011-11-03T11:33:27.594-04:002011-11-03T11:33:27.594-04:00Meena:
That's not correct, because RANK() can...Meena:<br /><br />That'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:<br /><br />With a table like this:<br />SELECT * FROM MY_DB.PRUEBA01 ORDER BY 1;<br /><br /><br /> *** Query completed. 9 rows found. 2 columns returned.<br /> *** Total elapsed time was 1 second.<br /><br /> ID_N C_TXT<br />----------- -------------------------<br /> 1 UNO<br /> 2 DOS<br /> 3 TRES<br /> 4 CUATRO<br /> 5 CINCO3<br /> 5 CINCO5<br /> 5 CINCO4<br /> 5 CINCO2<br /> 5 CINCO1<br /><br /> BTEQ -- Enter your SQL request or BTEQ command:<br /><br />your query will retrieve more rows then the specified 'TOP' (3 in this example):<br /><br />SELECT * FROM MY_DB.PRUEBA01 QUALIFY RANK() OVER (ORDER BY ID_N DESC) <=3;<br /><br /><br /> *** Query completed. 5 rows found. 2 columns returned.<br /> *** Total elapsed time was 1 second.<br /><br /> ID_N C_TXT<br />----------- -------------------------<br /> 5 CINCO3<br /> 5 CINCO4<br /> 5 CINCO2<br /> 5 CINCO1<br /> 5 CINCO5<br /><br />whereas ROW_NUMBER() will get the number of rows specified in the query (3):<br /><br /> BTEQ -- Enter your SQL request or BTEQ command:<br />SELECT * FROM DW_USUARIO.PRUEBA01 QUALIFY ROW_NUMBER() OVER (ORDER BY ID_N DESC) <=3;<br /><br /><br /> *** Query completed. 3 rows found. 2 columns returned.<br /> *** Total elapsed time was 1 second.<br /><br /> ID_N C_TXT<br />----------- -------------------------<br /> 5 CINCO1<br /> 5 CINCO3<br /> 5 CINCO4<br /><br />Cheers.<br /><br />carlos.Anonymousnoreply@blogger.com