How to get only one row for each distinct value in the column ?

Question

Hi Everybody,

I have a question about Access.If for example I have a table with the following data :

  NAME         | ADDRESS
  John Taylor  | 33 Dundas Ave.
  John Taylor  | 55 Shane Ave.
  John Taylor  | 786 Edward St.
  Ted Charles  | 785 Bloor St.
  Ted Charles  | 90 New York Ave.

I want to get one record for each person no matter of the address.For example :

  NAME        | ADDRESS
  John Taylor | 33 Dundas Ave.
  Ted Charles | 90 New York Ave.

Can this be done with queries only ? I tried using DISTINCT, but when I am selecting both columns, the combination is allways unique so I get all the rows.

Thank you !

Answer 1

If you do not care which address to show, then following should work:

SELECT  NAME,
        MIN(ADDRESS) AS ADDRESS
FROM    THETABLE
GROUP BY NAME

Answer 2

As a little extra, using MIN will return the first Address alphabetically. Access has 2 similar aggregate functions called FIRST and LAST which will return the first or last address according to the table's sort order.

For example if you take

ID | NAME | ADDRESS
1 | John Taylor | 55 Shane Ave.
2 | John Taylor | 786 Edward St.
3 | John Taylor | 33 Dundas Ave.
4 | Ted Charles | 785 Bloor St.
5 | Ted Charles | 90 New York Ave.

SELECT  NAME, 
    MIN(ADDRESS) AS ADDRESS 
FROM    THETABLE 
GROUP BY NAME 

will return

John Taylor | 33 Dundas Ave.
Ted Charles | 785 Bloor St.

SELECT  NAME, 
    FIRST(ADDRESS) AS ADDRESS 
FROM    THETABLE 
GROUP BY NAME 

will give you

John Taylor | 55 Shane Ave.
Ted Charles | 785 New York Ave.

hth

Ben