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?

 

[bobsan42]

I cannot understand exactly what you are after.
Why do two selects?

SELECT DISTINCT TOP 1 Country FROM Table WHERE ........ ORDER BY Country

??????
or this

SELECT DISTINCT Country FROM Table WHERE Country='Bulgaria'

 

[Joe4]

It might be best if you give us a simple example, so we can see exactly what you are trying to do.

 

[jscranton]

Thanks. I have Table where one field is an identifier. I want to return one instance of each identifier. The table also contains other fields (DOB, city, state, etc). I don't care about those fields at all. I only want to return one instance of each unique identifier. However, I want to return all fields in the table. Does that make sense?

 

[Joe4]

For values with the same identifier, are all the other fields the same value?
If not, then how do you decide which ones to return?
Does your table have a unique primary key? If so, what is it?

 

[jscranton]

Joe,

No. All other fields values are not the same. I have a primary key (ID). I would be happy just to return the first instance of value (sorted on ID).



Justin

 

[bobsan42]

I am almost totally confused.
do you mean something like this?

 SELECT Country, Fisrst(ID) AS FirstID, First(DOB) AS FirstOfDOB, First(City) AS FirstOfCity, First(State) AS FirstOfState, ...... FROM Table GROUP BY Country

 

[jscranton]

Let me try again.

I can run this:

Select Distinct Table.[Identifier]
From Table.[Identifier]

Which returns me a unique list of each Identifier in my Table.

However, I want to return a unique instance of each identifier AND all other other fields associated with that instance.

Think of it like deduplicating a spreadsheet on one column. The dedupe would leave the first instance of each value but remove the rest.

 

[xenou]

One solution is to use two queries:

First Query (called Query25):

SELECT Max(ID) as MaxOfID, CountryID 
FROM Table9
GROUP BY CountryID

This query gets a unique country code. I am using an autonumber field to ensure there is only one unique record for the country here (that can match one unique record in the table for that country). The use of autonumber is arbitrary. What you really need is either an autonumber or something based on the PRIMARY KEY.

Second query (called Query26):

SELECT t9.ID, t9.CountryID, t9.OtherStuff, t9.MoreStuff
FROM Table9 t9 
INNER JOIN Query25 q25 
ON t9.ID = q25.MaxOfID;

Results are:
-------------------------------------------
| ID | CountryID | OtherStuff | MoreStuff |
-------------------------------------------
|  4 | CAN       | stu        | vwx       |
|  2 | USA       | ghi        | jkl       |
-------------------------------------------

My original table Data (in Table9):
-------------------------------------------
| ID | CountryID | OtherStuff | MoreStuff |
-------------------------------------------
|  1 | USA       | abc        | def       |
|  2 | USA       | ghi        | jkl       |
|  3 | CAN       | mno        | pqr       |
|  4 | CAN       | stu        | vwx       |
-------------------------------------------

 

[Joe4]

I think Bobsan42's reply back in post 7 should do what you want, in just one query.
Just Group on the Identifier field, and take the first instance of all the other fields that you want to see.