Question about Distinct

[Min]

Hi, I need a query which contains 5 columns but I only need select records
that only two columns are distinct. How can do this?
Thanks!

 

[Dirk Goldgar]

Hi, I need a query which contains 5 columns but I only need select
records that only two columns are distinct. How can do this?
Thanks!

I'm not sure I understand what you're asking. Could you give an example
of source data and the desired output of the query?

Note, by the way, that since this is a query question, it would have
been better posted in the newsgroup devoted to queries:
<microsoft.public.access.queries>.

 

[Min]

Thanks for your reply.
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 899 890
3 bdc yyr2 797 378 379
4 bdc yyr2 794 375 359
5 bdc yyr2 494 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

If I put:

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

I will got all rows, which is not what I want.

Hope I made the question clear. Is this possible?

Min

 

[Dirk Goldgar]

Thanks for your reply.
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 899 890
3 bdc yyr2 797 378 379
4 bdc yyr2 794 375 359
5 bdc yyr2 494 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

If I put:

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

I will got all rows, which is not what I want.

Hope I made the question clear. Is this possible?

It is possible *if* you can define which record should get picked from
among those records with the same values in col1 and col2. Why it is
that record number 1 should be returned and not record number 2, and why
should record number 3 be returned and not 4 or 5? The decision must be
made on the basis of some data in the record. Is the query to return
the one with the lowest value in the autonumber key column?

 

[Min]

Thanks for point out my ignore.
Actually, it doesn't mater that rows 1 and 3, or rows 2 and 4, or rows 1 and
5, ...are selected, only requirement is col1 and col2 must be unique.
Min

 

[Dirk Goldgar]

Thanks for point out my ignore.
Actually, it doesn't mater that rows 1 and 3, or rows 2 and 4, or
rows 1 and 5, ...are selected, only requirement is col1 and col2 must
be unique.
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.

 

[Min]

Thank you very much!

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
www.datagnostics.com

(please reply to the newsgroup)