Select Distinct query 1

[JonoB]

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 thought of doing was as follows, but I am not sure if this would actually work

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

I did try it out, and it runs very very slowly - there are 100,000 records or so...

Any ideas on how I can get the results that I am looking for?

Many thanks for your help.

 

[Remou]

Why can you not select both FullString and MergeString? If there are a number of FullStrings for each MergeString have you any preference for which is returned?

 

[JonoB]

The reason why I cannot select both FullString and MergeString is that *every* FullString is unique.

i.e. if I do
SELECT DISTINCT FullString, MergeString from qryMergeTime;

or if I do
SELECT DISTINCT FullString from qryMergeTime;

then I will return every single record. Which is not what I am trying to achieve.

 

[PHV]

You may try either:
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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JonoB]

Perfect!

Sometimes the most simple answers are the right ones.