Access - Select distinct records where only one column is distinct

I have an Access table with two columns (ID and Active) with data like the following:

ID  |  Active
------------
123 | 0
124 | 0
125 | 0
123 | 1
314 | 1
948 | 1

I want to select the distinct records that have a unique ID (that only exist once, not just the first time they exist), but I also need the Active value. If I do a

SELECT DISTINCT ID from table1

I get the unique IDs, but not the sheet. It also returns 123 which isn't unique in the table. If I do:

SELECT DISTINCT * from table1

I get duplicate IDs if they have different Active values. I need a query to get the unique IDs and their associated Sheet value. It w开发者_JS百科ould return the following:

ID  |  Active
------------
124 | 0
125 | 0
314 | 1
948 | 1

Do I need to put these into two different tables and do an outer join? Active is only ever 0 or 1.

---

Use this:

SELECT *
FROM table1
WHERE Id IN (SELECT Id FROM table1 GROUP BY Id HAVING COUNT(Id)=1)

---

For anyone else, if you want at least one record returned for each ID, omitting any duplicates thereafter. A query similar to CesarGon's subquery will work, provided you want the first or last result for Active:

SELECT ID, First(table1.Active) as Active FROM table1 GROUP BY ID;

That will get you want you want:

ID  |  Active
------------
123 | 0
124 | 0
125 | 0
314 | 1
948 | 1

If you want the last value for Active:

SELECT ID, Last(table1.Active) as Active FROM table1 GROUP BY ID;

That will get you:

ID  |  Active
------------
123 | 1
124 | 0
125 | 0
314 | 1
948 | 1

---

Try SELECT DISTINCT ID, Max(ACTIVE) from table1

To give

ID | Active

124 | 0 125 | 0 123 | 1 314 | 1 948 | 1