SQL query with distinct

[Smarty]

Is it possible to return all elements of all rows where you check the distinct only for one field?

SELECT DISTINCT DatasetName FROM XCA_DatasetDef WHERE ProjectID=5, will only return the DatasetName

SELECT DISTINCT * FROM XCA_DatasetDef WHERE ProjectID=5, will return all rows where there is some distinction in one field or another.

I need all rows with all fields where the ProjectID = 5 and the DatasetName is different...

Thanx anyway!

 

[RickCole]

How about:

SELECT DatasetName FROM XCA_DatasetDef WHERE ProjectID=5
group by DatasetName

Rick

 

[Smarty]

This also gives me only the DatasetName... I want all fields of any record with that name and projectID

Thanx already

 

[RickCole]

Sorry rushed that one off somewhat how about:

select * from XCA_DatasetDef where ProjectID=5
and DatasetName in (select distinct DatasetName from XCA_DatasetDef where ProjectID=5)

Rick.

 

[Smarty]

I guess this is of no use because when i have two records with a particular DatasetName, they will both be returned, and i only want one of the two (no matther which one)

 

[RickCole]

Sorry Friday afternoon and need to get home so have no more time to offer any further help on this. But yes you can do what you require just need to get my head round it.

If know else picks up on this one I'll hopefully be able to resolve this in the morning when I have a little more free time.

Do you have a primary key column on the table by the way??

Rick.

 

[RickCole]

Assuming you have a primary key on you table say called pk_id then I think this might be what you want:

select * from XCA_DatasetDef a
where (select count( b.pk_id) from XCA_DatasetDef b
where (a.pk_id >= b.pk_id and
a.DatasetName = b.DatasetName and
ProjectID=5))= 1


Rick.