sql - select distinct and autoincrement field in select query

Question

I have a table Product with

ProductNo ProductDetail UniqueiD(Primarykey)
L1234      ProductA        1
L1234      ProductB        2
L1234      ProductC        3
M1234      ProductD        4
M1234      ProductE        5

So i need a select query that will display distinct product no with ids for displaying in p-listbox. say

Name  code
L1234  1
M1234  2

How do i achieve this? Thanks

Answer 1

One method is:

select distinct name, dense_rank() over (order by name)
from product;

That said, I would probably use group by:

select name, row_number() over (order by name) as code
from product
group by name;