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.

Dont't use Rank() function when you are select First N records..

ReplyDeleteUse 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.

use the TOP with order by.

ReplyDeleteThere is nothing like the BEST way to get top n records is.. this way.. or that way.

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