CODEWITHSUNDEEP
sqlsql-servert-sql
madphp
madphp

Reputation: 1764

Return all Fields and Distinct Rows

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.

Upvotes: 0

Views: 763

Answers (6)

GilaMonster
GilaMonster

Reputation: 1768

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

Upvotes: 0

Raj More
Raj More

Reputation: 48016

You can do

SELECT DISTINCT Name, Address, Max (Address1), Max (City)
FROM table

Use @JBrooks answer below. He has a better answer.

Return all Fields and Distinct Rows

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146469

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...

Upvotes: 2

JBrooks
JBrooks

Reputation: 10013

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

Upvotes: 1

dnagirl
dnagirl

Reputation: 20456

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.

Upvotes: 1

Lawrence Barsanti
Lawrence Barsanti

Reputation: 33232

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).

Upvotes: 0

Related Questions