SELECT DISTINCT... sort of (1 Viewer)

[jan@BRU]

Hello,
I have the following query problem. Consider the following table (field names and content):

Code, Name, OtherInfo

A1, Peter, Soandso
A2, Paul, Blabla
A1, Mary, Othertext
A1, Jack, Otherstuff


What I'd like to get back from my query is:

A1, Peter, Soandso
A2, Paul, Babla

In other words: I want to do a SELECT DISTINCT on the Code field, but still bring up the other fields in the table. As to name, it should bring up the First one, and for OtherInfo, it should just bring up the one which is associated with the first name.


Intuitively you would think that something like that should work:

SELECT Code, First(Name), OtherInfo
FROM Table
GROUP BY Code

Unfortunately it doesn't, as the Group By requires all fields to have an aggregate function ...

Any advice?

Many thanks

 

[namliam]

SELECT Code, First(Name), First(OtherInfo), etc???
FROM Table
GROUP BY Code

 

[jan@BRU]

Thanks to both of you for your quick reply:

1) Yes, it does indeed work for the selecting the First (as in: First in the recordset) name and if there is only one (or few "don't care" fields, which need to be reproduced. This way of doing it won't work, if I were to select e.g. the Min() (as in: alphatical lowest name) for example, if I wanted to get :

A1, Jack, Otherstuff
A2, Paul, Babla

(Jack being alphabetically in front of Paul) and if there are more than 10 don't cares... Or am I overlooking something

... oops ... there was a second reply before, suggesting, I should do a first query and than reference back to the original table, but now it's gone ....
2) Many thanks for the second solution, too. How would I do the nested SQL?

Thanks so much again

Jan

 

[Brianwarnock]

I honestly don't understand what you are trying to do here, but that may well be me. I am posting to warn you about the use of First and Last which have been the subject of discussions on this forum.
they produce inconsistent results.
From help

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

Brian

 

[jan@BRU]

Thanks for that. I sincerely appreciate the warning.

What I'm actually trying to do is the following:

(Sort of) retrieving distinct records from a table. The thing is, I only want them to be distinct on one (or say a couple of) field(s) ... the "Code" field in my example. There are other fields in the table, for which I don't care, whether they are the same or different in the original table. So, while I don't want to select the (distinct) records without consideration for these 'don'tcare' fields, I still want to reproduce them in my selection.

This, of course, produces the problem that if - indeed - the don't care fields ARE different, my query needs to decide, the don't care fields of which record to retrieve. For that, I suggested (just for the purpose of the argument) to retrieve the 'First' of this otherwise non-distinct records..

Gosh, I hope, I'm making myself understood.

All help is VERY welcome.

 

[Brianwarnock]

I sort of understood that but you did seem to care which name was chosen and I don't see how you can be sure of that.

Brian

 

[jan@BRU]

That is precisely the issue. Let's say I wanted to choose the first in alphabet...

 

[Alc]

How about sorting by Name, then using the Min() function?

 

[Brianwarnock]

No need to sort to use the min or max functions, so every thing is fine if the first or last alphabetically is what you want.

Brian

 

[neileg]

I posted the second solution and then realised namlian had a more elegant answer that did the same thing.

You seem to be unclear about which of the matching records you want. Access can only answer precise questions and can't cope with fuzzyness! So you need think out exactly what the question is.

I think the help file is misleading on First and Last. Chronological order only applies if the data is original and unmolested. Sorting a table only changes the display of the data not the storeage, hence the comment about sorting not chnaging the result. If you sort the data in a query and use the query as the source for your First/Last query, then it is the order in the query that matters. If your source is not a Jet table, perhaps an Excel spreadsheet and the spreadsheet has be re-ordered, then it is the new order that applies. These are some of the reasons why First and Last appear to produce inconsistent results.