Select distinct but return all fields

[jscranton]

I want to select a single distinct example based on one field in a table. Something like:

Select table.*
From (Select distinct table.[country] from table)
Where( . . . .)

Is this possible in access sql?

 

[xenou]

Yes, that also works for me. Incidentally works with Last as well as First:

SELECT CountryID, First(ID) AS FirstID, First(OtherStuff) AS FirstOfOtherStuff, First(MoreStuff) AS FirstOfMoreStuff FROM Table9 GROUP BY CountryID
SELECT CountryID, Last(ID) AS LastID, Last(OtherStuff) AS LastOfOtherStuff, Last(MoreStuff) AS LastOfMoreStuff FROM Table9 GROUP BY CountryID

Respectively (using my dataset from above):
--------------------------------------------------------------
| CountryID | FirstID | FirstOfOtherStuff | FirstOfMoreStuff |
--------------------------------------------------------------
| CAN       |       3 | mno               | pqr              |
| USA       |       1 | abc               | def              |
--------------------------------------------------------------

-----------------------------------------------------------
| CountryID | LastID | LastOfOtherStuff | LastOfMoreStuff |
-----------------------------------------------------------
| CAN       |      4 | stu              | vwx             |
| USA       |      2 | ghi              | jkl             |
-----------------------------------------------------------

 

[bobsan42]

So maybe the query in post #7 is what you are after.
There can be variations - instead of FIRST you can use LAST. Depending on your data you can use MIN or MAX but this may (an probably will) return values from different records.