SQL Help: Select distinct

[Matt Pegg]

Hi all,

This I thought would be a relatively easy question, but after hunting around the net, I haven't been able to source an answer..

I have a large table in Access with four fields.

I would like to generate a result set that includes all four columns,
but removes duplicates for the last 3 columns. eg:

something like

SELECT Column1, DISTINCT Column2, DISTINCT Column3, DISTINCT Column4 from Table_X;

can anyone help with the format ?

Note: Don't care what the value for Column1 is as long as it comes from one of the duplicates...

cheers,
Matt.

[JimInAJam]

select col1, col2, col3, col4
from tbl
group by col1

or it may be the other way round

group by col2,col3,col4

you get the drift.

[Matt Pegg]

select col1, col2, col3, col4
from tbl
group by col1

or it may be the other way round

group by col2,col3,col4

you get the drift.

Thanks for the response.

Unfortunately in order to use GROUP BY, I need to iclude all of the fields selected in the GROUP BY clasue which defeats the purpose...

ie. tried SELECT Column1, Column2, Column3, Column4 from Table_X GROUP BY Column2, Column3, Column4;

but got "You tried to execute a query that does not include the specified expression "Column1" as part of an aggregate function. (Error 3122)"


regards,
Matt.

[Rabbit]

You'll need two queries. One to select distinct on all fields. A second to right join the query back with the original table on the first field.

[NeoPa]

I would like to generate a result set that includes all four columns, but removes duplicates for the last 3 columns.

What do you mean by removing duplicates for the three columns?
What if there are duplicates in Coluimn B but attached to records where Column C is unique?

Perhaps you could provide some example data with your explanation, as well as the results you would require to be returned.