Select distinct but return all fields

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
I cannot understand exactly what you are after.
Why do two selects?
Code:
SELECT DISTINCT TOP 1 Country FROM Table WHERE ........ ORDER BY Country
??????
or this
Code:
SELECT DISTINCT Country FROM Table WHERE Country='Bulgaria'
:)
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
It might be best if you give us a simple example, so we can see exactly what you are trying to do.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 30, 2011
Messages
707
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,364

ADVERTISEMENT

I am almost totally confused.
do you mean something like this?
Code:
 SELECT Country, Fisrst(ID) AS FirstID, First(DOB) AS FirstOfDOB, First(City) AS FirstOfCity, First(State) AS FirstOfState, ...... FROM Table GROUP BY Country
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
One solution is to use two queries:

First Query (called Query25):
Code:
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):
Code:
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       |
-------------------------------------------
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top