Rank Row

One of the developer came to me yesterday. she was asking "I have some duplicate rows in col1 but not in col2 how can i just want to display the first row?


Here is the sample table so you can imagine it

me@d012band> select cust_no, name1 from vd.t2;

CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900000377 ABC 2
0000000900000377 ABC 3
0000000900001059 DEF 1
0000000900001059 DEF 2
0000000900001059 DEF 3
0000000900001059 BEA 1
0000000900003125 WER 1
0000000900003125 WER 2
0000000900003125 WER 3
0000000900004777 AFG 1
0000000900004777 AFG 2
0000000900004777 AFG 3

13 rows selected.


the output that she wanted to is something like this


CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1


So, what i did is run this query and bingo we got the answer she wanted to


me@d012band> select cust_no, name1 from
2 (select cust_no, name1,rank() over (partition by cust_no order by rowid) rank_n from vd.t2)
3 where rank_n = 1;


CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1



I used rank() function to rank every row, thanks to Oracle that build this kind function so that can make our query easier


Have a nice day


FZheng