Return all Fields and Distinct Rows

Question

Whats the best way to do this, when looking for distinct rows?

SELECT DISTINCT name, address 
  FROM table;

I still want to return all fields, ie address1, city etc but not include them in the DISTINCT row check.

Answer 1

Then you have to decide what to do when there are multiple rows with the same value for the column you want the distinct check to check against, but with different val;ues in the other columns. In this case how does the query processor know which of the multiple values in the other columns to output, if you don't care, then just write a group by on the distinct column, with Min(), or Max() on all the other ones..

EDIT: I agree with comments from others that as long as you have multiple dependant columns in the same table (e.g., Address1, Address2, City, State ) That this approach is going to give you mixed (and therefore inconsistent ) results. If each column attribute in the table is independant ( if addresses are all in an Address Table and only an AddressId is in this table) then it's not as significant an issue... cause at least all the columns from a join to the Address table will generate datea for the same address, but you are still getting a more or less random selection of one of the set of multiple addresses...

Answer 2

I don't think you can do this because it doesn't really make sense.

name | address | city | etc...
abc  | 123     | def  | ...
abc  | 123     | hij  | ...

if you were to include city, but not have it as part of the distinct clause, the value of city would be unpredictable unless you did something like Max(city).

Answer 3

When you have a mixed set of fields, some of which you want to be DISTINCT and others that you just want to appear, you require an aggregate query rather than DISTINCT. DISTINCT is only for returning single copies of identical fieldsets. Something like this might work:

SELECT name, 
       GROUP_CONCAT(DISTINCT address) AS addresses, 
       GROUP_CONCAT(DISTINCT city) AS cities
FROM the_table
GROUP BY name;

The above will get one row for each name. addresses contains a comma delimted string of all the addresses for that name once. cities does the sames for all the cities.

However, I don't see how the results of this query are going to be useful. It will be impossible to tell which address belongs to which city.

If, as is often the case, you are trying to create a query that will output rows in the format you require for presentation, you're much better off accepting multiple rows and then processing the query results in your application layer.

Answer 4

This will not mix and match your city, state, etc. and should give you the last one added even:

select b.*
from (
    select max(id) id, Name, Address
    from table a
    group by Name, Address) as a
inner join table b
on a.id = b.id

Answer 5

If you're using SQL Server 2005 or above you can use the RowNumber function. This will get you the row with the lowest ID for each name. If you want to 'group' by more columns, add them in the PARTITION BY section of the RowNumber.

SELECT id, Name, Address, ...
(select id, Name, Address, ...,  
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY id) AS RowNo
  from table) sub
WHERE RowNo = 1