CODEWITHSUNDEEP
sqlms-accessunique
HeXor
HeXor

Reputation: 1125

MS-Access select first row of non-unique ID

How can I select the first dataset, of two multiple rows with the same non-unique ID in MS-Access?

I did not design this mess with a non unique ID, and other fields than ID are random string values, so could be unique or duplicates.

Say I have the input table

Tab_INPUT
--------------------------
| ID | VAL1 | VAL2 | ... | 
- - - - - - - - - - - - - 
| 1  | "A"  | "1a" | ... |
| 2  | "B"  | "2b" | ... |
| 2  | "C"  | "2a" | ... |
| 3  | "D"  | "3c" | ... |
| 3  | "E"  | "3b" | ... |
| 3  | "F"  | "3a" | ... |
| 4  | "G"  | "4a" | ... | 
--------------------------

I now want to select the first row for each distinct ID, so i.e.

Tab_OUTPUT (DESIRED)
--------------------------
| ID | VAL1 | VAL2 | ... |
- - - - - - - - - - - - - 
| 1  | "A"  | "1a" | ... |
| 2  | "B"  | "2b" | ... |
| 3  | "D"  | "3c" | ... |
| 4  | "G"  | "4a" | ... |
--------------------------

I can not use something like

SELECT ID, Min(VAL1), Min(Val2) FROM Tab_INPUT GROUP BY ID

since this would mess up the rows itsself

Tab_OUTPUT (WRONG)
--------------------------
| ID | VAL1 | VAL2 | ... |
- - - - - - - - - - - - - 
| 1  | "A"  | "1a" | ... |
| 2  | "B"  | "2a" | ... |
| 3  | "D"  | "3a" | ... |
| 4  | "G"  | "4a" | ... |
--------------------------

I need the rows itsself to stay intact, just selecting the first with a unique ID

Upvotes: 2

Views: 2413

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Try using First instead of Min:

SELECT ID, First(VAL1) AS Val1, First(Val2) AS Val2
FROM Tab_INPUT 
GROUP BY ID

Upvotes: 2

Related Questions