I have the following:

ID            Name
1000        Smith
1000        Jackson
1000        Fastow
1001        Carr
1002        Michaels

I want the following output: Only distinct ID and first Name
1000        Smith
1001        Carr
1002        Michaels

H

Define "first".  Tables have no order to them.  If you want the highest
alphabetically per ID, try:

select
    ID
,    max (Name) Name
from
    MyTable
group by
    ID

I have the following:

ID            Name
1000        Smith
1000        Jackson
1000        Fastow
1001        Carr
1002        Michaels

I want the following output: Only distinct ID and first Name
1000        Smith
1001        Carr
1002        Michaels

H

The context "First" doesn´t exists if you have no column to order
from. There is no predefined / natural order in the tables. So assuming
that it is makes no matter what name you are getting I use the
aggregate MIN() for this-

SELECT ID,MIN(Name)
FROM SomeTable
GROUP BY Id

HTH, Jens Suessmeyer.

The context "First" doesn´t exists unless you use the word ORDER or
another mechanism. There is no predefined / natural order in the
tables. Assuming that you want to get the MIN() name I used this
aggregate for the query:

SELECT Id, MIN(Name)
FROM Sometable
GROUP BY ID


HTH, jens Suessmeyer.

You have distinct ID and name already. (are these first names?)

What makes Smith special for id 1000?
Why that one and not Jackson or Fastow?

and before you say (if you were) "because it's the first in the list" -
this is an rdbms - not a list. there is no such thing as "first" without
a specified order.

Please post DDL so we'll know what other criteria makes Smith special, etc.


Tim wrote:
Show quoteHide quote

OPTIMIZER LOCK HINTS
Select most recent date time
What Are The Issues With MS Access Client to SQL Server DB
Column X is same, column Y is different?
Imbedded subroutine prefix error