Tuesday, February 15, 2011

How to select first N Records in Teradata?


How to select first N Records in Teradata?

Answers:
To select N records in Teradata you can use RANK function. Query syntax would be as follows
SELECT BOOK_NAME, BOOK_COUNT, RANK(BOOK_COUNT) A FROM LIBRARY QUALIFY A <= 10;

3 comments:

  1. Dont't use Rank() function when you are select First N records..
    Use the ROW_NUMBER() Function..

    The Difference b/w RANK() and ROW_NUMBER is:

    SELECT
    EMP_NUM,
    RANK() OVER (ORDER BY EMP_NUM)AS RANK_F,
    ROW_NUMBER() OVER (ORDER BY EMP_NUM)AS ROWNUM_F
    FROM EMPLOYEE;

    ------------------------------------------------
    EMP_NUM --- RANK_F --- ROWNUM_F
    250 ------- 1 ----- 1
    250 ------- 1 ----- 2
    300 ------- 3 ----- 3
    350 ------- 4 ----- 4
    400 ------- 5 ----- 5
    400 ------- 5 ----- 6
    900 ------- 7 ----- 7

    See the First 2 record(and 5,6th records). Rank() function for both two records has value 1
    but ROW_NUMBER() function for both two records has diffrent.( First row 1, Second row 2)
    So...Rank() Function is work with respective to "what data" is present in that column.
    And ..ROW_NUMBER() Function is work "irrespective" of what data is present in that column.

    ReplyDelete
  2. There is nothing like the BEST way to get top n records is.. this way.. or that way.

    you can use top, order by , rown_num, rank.. so on ...

    ReplyDelete