Select Distinct query

[Guest]

I have a select distinct query as follows:

SELECT DISTINCT [MergeString] FROM qryMergeTime

Whilst this does return the right records (i,e. only shows unique records in
the [MergeString] field, it doesnt show all the fields that I need to see,
i.e. the SELECT DISTINCT function needs to run on the [MergeString] field,
but I want to see the results from the [FullString] field.

So, what I have done is as follows:

SELECT DISTINCT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

However, as I am working with a relatively large dataset (few hundred
thousand records), the query takes a long time to run.

Any ideas on how I can improve this?

Many thanks for your help.

 

[Guest]

Oops, didnt quite post that right...should be:

SELECT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

 

[John Spencer]

That query makes little sense to me. The following query would seem to me
to give you the same results as what you posted

SELECT FullString
FROM qryMergeTime
WHERE MergeString Is Not Null



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Oops, didnt quite post that right...should be:

SELECT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

I have a select distinct query as follows:

SELECT DISTINCT [MergeString] FROM qryMergeTime

Whilst this does return the right records (i,e. only shows unique records
in
the [MergeString] field, it doesnt show all the fields that I need to
see,
i.e. the SELECT DISTINCT function needs to run on the [MergeString]
field,
but I want to see the results from the [FullString] field.

So, what I have done is as follows:

SELECT DISTINCT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

However, as I am working with a relatively large dataset (few hundred
thousand records), the query takes a long time to run.

Any ideas on how I can improve this?

Many thanks for your help.

 

[Guest]

How is that going to return DISTINCT results from the MergeString field?

That query makes little sense to me. The following query would seem to me
to give you the same results as what you posted

SELECT FullString
FROM qryMergeTime
WHERE MergeString Is Not Null



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Oops, didnt quite post that right...should be:

SELECT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

I have a select distinct query as follows:

SELECT DISTINCT [MergeString] FROM qryMergeTime

Whilst this does return the right records (i,e. only shows unique records
in
the [MergeString] field, it doesnt show all the fields that I need to
see,
i.e. the SELECT DISTINCT function needs to run on the [MergeString]
field,
but I want to see the results from the [FullString] field.

So, what I have done is as follows:

SELECT DISTINCT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM qryMergeTime);

However, as I am working with a relatively large dataset (few hundred
thousand records), the query takes a long time to run.

Any ideas on how I can improve this?

Many thanks for your help.

 

[John Spencer]

It isn't but that should make no difference in the results you are
returning.

Your subquery returned a distinct list of all the mergestring values. So if
you had the following values in mergestring (6 records total)
a, b, c, a, a, b

You would get three rows returned a, b, and c by the distinct query.

Now get records that have a, b, or c in mergestring. --- That would be the
six records you started with.

That should give the same exact results as the one I posted using is Not
Null.

Perhaps you can explain what you are trying to accomplish. Don't worry
about the exact SQL. For instance, are you trying to get fullstring if
mergestring only exists ONE time in the qryMergeTime (in the six record
example, that would return the record with mergestring = "C")?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How is that going to return DISTINCT results from the MergeString field?

That query makes little sense to me. The following query would seem to
me
to give you the same results as what you posted

SELECT FullString
FROM qryMergeTime
WHERE MergeString Is Not Null



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Oops, didnt quite post that right...should be:

SELECT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM
qryMergeTime);

:

I have a select distinct query as follows:

SELECT DISTINCT [MergeString] FROM qryMergeTime

Whilst this does return the right records (i,e. only shows unique
records
in
the [MergeString] field, it doesnt show all the fields that I need to
see,
i.e. the SELECT DISTINCT function needs to run on the [MergeString]
field,
but I want to see the results from the [FullString] field.

So, what I have done is as follows:

SELECT DISTINCT [FullString]
FROM qryMergeTime
WHERE [MergeString] In (SELECT DISTINCT [MergeString] FROM
qryMergeTime);

However, as I am working with a relatively large dataset (few hundred
thousand records), the query takes a long time to run.

Any ideas on how I can improve this?

Many thanks for your help.

 

[Guest]

OK, let me try explain a bit better

Assume that I have a tablewith only one field: MergeString

What I would like to do is only return unique records from the MergeString
field. The following sql achieves this, and works perfectly.

SELECT DISTINCT [MergeString] FROM tblMergeTime

Now, lets take it a step further, and say that there are in fact two fields
in the table: FullString and MergeString

I would still like to only return unique records on the MergeString field,
however, I want to actually show the FullString field for these unique
records.

i.e. show me all FullString records where MergeString records are unique.

Hope this makes more sense.

 

[John Spencer]

Going back to my 6 record example table and expanding on it to show the two
columns of data

A AAAA
A AAXX
B BBBB
B BBBB
B XXXX
C CCXX

Which of those six records do you want to see?

Do you only want to see the 6th record? The "C" value is the only one that
is not repeated in the merge column.
Do you want to see records 1,2,4, and 6? The combination of the two columns
only appears once - B BBBB appears twice.
Do you want to see all the records but combine the B BBBB records into one?
Or do none of the above fit?

-
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK, let me try explain a bit better

Assume that I have a tablewith only one field: MergeString

What I would like to do is only return unique records from the MergeString
field. The following sql achieves this, and works perfectly.

SELECT DISTINCT [MergeString] FROM tblMergeTime

Now, lets take it a step further, and say that there are in fact two
fields
in the table: FullString and MergeString

I would still like to only return unique records on the MergeString field,
however, I want to actually show the FullString field for these unique
records.

i.e. show me all FullString records where MergeString records are unique.

Hope this makes more sense.

It isn't but that should make no difference in the results you are
returning.

Your subquery returned a distinct list of all the mergestring values. So
if
you had the following values in mergestring (6 records total)
a, b, c, a, a, b

You would get three rows returned a, b, and c by the distinct query.

Now get records that have a, b, or c in mergestring. --- That would be
the
six records you started with.

That should give the same exact results as the one I posted using is Not
Null.

Perhaps you can explain what you are trying to accomplish. Don't worry
about the exact SQL. For instance, are you trying to get fullstring if
mergestring only exists ONE time in the qryMergeTime (in the six record
example, that would return the record with mergestring = "C")?

--

 

[Guest]

Hi John,

A AAAA
A AAXX
B BBBB
B BBBB
B XXXX
C CCXX

I would want to see records 1, 2, 4, 5, 6 - they are each unique in the
second field. If the case of non-unique records in the second field (such as
records 3 and 4), I would want the second record of the two, if at all
possible. (Note that there would never be more than 2 matched records in the
second column).

In addition, in reality, every record in field A will always be unique, so
let me give you a better example:

A AAA
B AAA
C BBB
D BBB
E XXX
F XXX
G YYY

In the above case, I would want records 2, 4, 6, 7

Going back to my 6 record example table and expanding on it to show the two
columns of data

A AAAA
A AAXX
B BBBB
B BBBB
B XXXX
C CCXX

Which of those six records do you want to see?

Do you only want to see the 6th record? The "C" value is the only one that
is not repeated in the merge column.
Do you want to see records 1,2,4, and 6? The combination of the two columns
only appears once - B BBBB appears twice.
Do you want to see all the records but combine the B BBBB records into one?
Or do none of the above fit?

-
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK, let me try explain a bit better

Assume that I have a tablewith only one field: MergeString

What I would like to do is only return unique records from the MergeString
field. The following sql achieves this, and works perfectly.

SELECT DISTINCT [MergeString] FROM tblMergeTime

Now, lets take it a step further, and say that there are in fact two
fields
in the table: FullString and MergeString

I would still like to only return unique records on the MergeString field,
however, I want to actually show the FullString field for these unique
records.

i.e. show me all FullString records where MergeString records are unique.

Hope this makes more sense.

It isn't but that should make no difference in the results you are
returning.

Your subquery returned a distinct list of all the mergestring values. So
if
you had the following values in mergestring (6 records total)
a, b, c, a, a, b

You would get three rows returned a, b, and c by the distinct query.

Now get records that have a, b, or c in mergestring. --- That would be
the
six records you started with.

That should give the same exact results as the one I posted using is Not
Null.

Perhaps you can explain what you are trying to accomplish. Don't worry
about the exact SQL. For instance, are you trying to get fullstring if
mergestring only exists ONE time in the qryMergeTime (in the six record
example, that would return the record with mergestring = "C")?

--

 

[Guest]

Sometimes the right answer is the most simple one:

SELECT MergeString, First(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Last(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Min(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Max(FullString) FROM qryMergeTime GROUP BY MergeString

Works fine for me.

Thanks for trying to work out what I was trying to do anyway.

Hi John,

A AAAA
A AAXX
B BBBB
B BBBB
B XXXX
C CCXX

I would want to see records 1, 2, 4, 5, 6 - they are each unique in the
second field. If the case of non-unique records in the second field (such as
records 3 and 4), I would want the second record of the two, if at all
possible. (Note that there would never be more than 2 matched records in the
second column).

In addition, in reality, every record in field A will always be unique, so
let me give you a better example:

A AAA
B AAA
C BBB
D BBB
E XXX
F XXX
G YYY

In the above case, I would want records 2, 4, 6, 7

Going back to my 6 record example table and expanding on it to show the two
columns of data

A AAAA
A AAXX
B BBBB
B BBBB
B XXXX
C CCXX

Which of those six records do you want to see?

Do you only want to see the 6th record? The "C" value is the only one that
is not repeated in the merge column.
Do you want to see records 1,2,4, and 6? The combination of the two columns
only appears once - B BBBB appears twice.
Do you want to see all the records but combine the B BBBB records into one?
Or do none of the above fit?

-
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK, let me try explain a bit better

Assume that I have a tablewith only one field: MergeString

What I would like to do is only return unique records from the MergeString
field. The following sql achieves this, and works perfectly.

SELECT DISTINCT [MergeString] FROM tblMergeTime

Now, lets take it a step further, and say that there are in fact two
fields
in the table: FullString and MergeString

I would still like to only return unique records on the MergeString field,
however, I want to actually show the FullString field for these unique
records.

i.e. show me all FullString records where MergeString records are unique.

Hope this makes more sense.


:

It isn't but that should make no difference in the results you are
returning.

Your subquery returned a distinct list of all the mergestring values. So
if
you had the following values in mergestring (6 records total)
a, b, c, a, a, b

You would get three rows returned a, b, and c by the distinct query.

Now get records that have a, b, or c in mergestring. --- That would be
the
six records you started with.

That should give the same exact results as the one I posted using is Not
Null.

Perhaps you can explain what you are trying to accomplish. Don't worry
about the exact SQL. For instance, are you trying to get fullstring if
mergestring only exists ONE time in the qryMergeTime (in the six record
example, that would return the record with mergestring = "C")?