"Select Distinct ..." or something else?

[Min]

Hi:

I have a table that has an auto number field as the key, and two columns
(say col1 and col2) that will have duplicated rows and other columns that is
quite unique.

I need select all columns, but I don't want to have duplicated col1 and
col2.

Let me put the Table1 as following:

No. col1 col2 col3 col4 col5
1 abc ttc1 789 234 235
2 abc ttc1 688 375 890
3 bdc yyr2 797 378 379
4 bdc yyr2 794 375 235
5 bdc yyr2 789 365 459


I need only rows 1 and 3 with all values from col1 to col5

abc ttc1 789 234 235
bdc yyr2 797 378 379

or rows 1 and 4 or rows 2 and 4, ... only requirement is col1 and col2 are
distinct.

If I put:

Select distinct col1, col2, col3, col4, col5 From Table1

I will got all rows, which is not what I want. Actually, I cannot use Where
clause, as there are in fact thousands of rows with several hundreds of
distinct rows that have unique col1 and col2 values.

How can do this?

 

[Duane Hookom]

Why values from row 1 and 3 and not 2 and 5?

 

[Min]

Thank you for your reply.
Acutally, there is no reason why should row 1 and 3 and but not 2 and 5,
only requirement is col1 and col2 must be distinct.

I've got the answer from Dirk Goldgar. Sorry for multipost. When I was told
it is better to post query question on this group, I did both, just want to
get early answer. Thank you anywhere.

Min
------------------------------------------------------------------------
As long as you are dealing with Access databases and Jet SQL, you can
use a query like this:

SELECT
col1, col2,
First(col3) AS col3, First(col4) AS col4, First(col5) AS col5
FROM Table1
GROUP BY col1, col2;

The First() function can't actually be guaranteed to return you the data
from the first record in each group (though it likely will if your table
has a primary key) -- but then, you say you don't actually care about
that.