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
精彩评论