"Select Distinct ..." or something else?

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?

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

--
Duane Hookom
MS Access MVP


"Min" wrote in message
...
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?

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.

--
Dirk Goldgar, MS Access MVP
------------------------------------------------------------


"Duane Hookom" дÈëÓʼþ
...
Why values from row 1 and 3 and not 2 and 5?

--
Duane Hookom
MS Access MVP


"Min" wrote in message
...
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?