DISTINCT on a single column

Hi,

I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

Example:

Name......ID

John.......1
John.......2
Mary.......3

I need only one record for John. But both Name and ID should be selected.

Thanks"I need only one record for John. But both Name and ID should be selected."

What value do you expect to be present in the ID field, given your example data?|||Any value in the ID field can be selected. It's only the name that matters.|||???

So what result to you want from:

John.......1
John.......2
Mary.......3

Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

Option C:
John, 1
, 2
Mary, 3

Your logic is not clear.

blindman|||Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

Thanks|||Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ?|||Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

Try using Limit or TOP|||You want a group by query.

Select Name, Min(ID)
from YourTable
Group By Name

blindman|||What's "Limit"?

blindman|||Here is the acual data

Name......ID...Dept..University...

John......17...A........XYZ
John......18...B........XYZ

Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

Thanks|||i think "limit" is in mysql and not in mssql ... wrong forum buddy|||Assuming ID is a unique value in your table (it better be, or your logic is not possible):

select YourTable.*
from YourTable
inner join
(select Name,
Min(ID) ID
from YourTable) DistinctIDs
on YourTable.ID = DistinctIDs.ID

This selects a single ID for each unique name and returns the data associated with that ID.

blindman|||"select Name,
Min(ID) ID
from YourTable"

Doesn't a Group By clause need to be here.|||Yeah, that would probably help... :rolleyes:

blindman