How to make distinc at only some fields of one select?

[Fursten]

Hi

I made a select at several fields from 3 tables. I would like to make DISTINCT on that select but only at 3 of those fields, manteining the others.

I´m using SQL server.

Is this possible?

Thank you

 

[MikeJones]

Does SQL Server come with a GROUP BY command??

 

[Fursten]

Yes, it already have a group by.

 

[grahamnewlands]

No you can't do this as a select distinct statement. As with all other sql statements they work on rows of data...It is a common mistake...The easiest way to do this is have a select distinct of the fields you wish i.e. this will get the distinct rows of data you wish...For the other fields you will have do a select within the outer select distinct loop because there could be more than one row retrieved which is why you are confused e.g.

select distinct field1, field2, field3
from table1
begin
select field3, field4
from table2
/* select the fields based on a key from the outer loop */
where field5 = :field1
and field6 = :field2
begin
<statements>
end
end

the above is only a guide but you may have to do a cursor select for the outer select as some languages are fussy that you can't have nested select loops...i.e. you have to do a cursor select as the outer loop

Hope this helps

 

[CRoberts]

Does Sql Server has something like an in-line view such as Oracle? In Oracle you could:

SELECT A.PrimaryKey, B.Fld1, C.Fld2, D.Fld3
FROM Original_Table a,
(Select DISTINCT PrimaryKey, Fld1
FROM Original_Table) B,
(Select DISTINCT PrimaryKey, Fld2
FROM Original_Table) C,
(Select DISTINCT PrimaryKey, Fld3
FROM Original_Table) C,
WHERE A.PrimaryKey = B.PrimaryKey
AND A.PrimaryKey = C.PrimaryKey;

 

[MalcolmW]

SQL Server calls em &quot;derived tables&quot;, but they use the same syntax - well, with the exception of your typo Malcolm