How to select distinct record?

1

Hi,

I have a table, as follows:

ID | Name | MyID

1 | Alan | 2

2 | Alan | 2

3 | John | 3

I want to select the records as follows:

  1.  If two records have the duplicate values in ‘Name’

column, then only the first record is selected and the other record is
discarded, even if the two records have different values in other columns.

  1.  If two records have different ‘Name’ value, then

they are ordered via ‘MyID’ column in ascendant order.

  1.  All columns of the records should be returned.

So for the above records, the query should select the
following ones:

ID | Name | MyID

1 | Alan | 2

3 | John | 3

How to write the query? It seems I should use DISTINCT or
GROUP BY, but my queries are all fail. Please help.

Thanks

2

use the ROW_NUMBER function

3 
SELECT ID, Name, MyID --,...
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS row_num
    FROM table_name
) AS derived
WHERE row_num = 1