SQL Database Query selecting different matching values ​​for one value

How can I get this kind of output from the table? If I have a table like the one below

First Name         Last Name

 1. John            Doe
 2. John           Ruggles
 3. Ricky          Rog
 4. kelly          Ali
 5. Ricky           Gyri


I want to show it below

First Name       Last Name

 1.John           Doe

 2. Kelly         Ali

 3. Ricky         Rog


As with every name, I want to display the last name. I want the name to appear only once. Please help me. Its tabular data, first and last name are different columns.


source to share

1 answer

You can use an analytic function row_number()

to determine if the last name has changed:

select  case
        when row_number() over (partition by FirstName 
                                order by FirstName, LastName) = 1 
            then FirstName
        else ''
        end as FirstName
,       LastName
from    YourTable
order by
,       LastName


Example in SQL Fiddle.



All Articles