Select a selection and group by a subset of columns

Question

I am using SQL Server 2008 and I am stuck in this vicious circle between DISTINCT and GROUP BY

I have the following dummy table myTable :

 ID Street City PostalCode ProjectID Date NameId 1 Bar Street Sunny Beach 666 7 25/08/2013 111 2 Sin Street Ibiza 999 5 12/06/2013 222 3 Bar Street Sunny Beach 666 7 07/08/2013 333 4 Bora Bora Bora Bora 1000 10 17/07/2013 444 5 Sin Street Ibiza 999 5 04/07/2013 555 

I want to get all the records (possibly the first entry) with different addresses (Street, City, PostalCode) and ProjectID. For example, the result here should be:

 ID Street City PostalCode ProjectID Date NameId 1 Bar Street Sunny Beach 666 7 25/08/2013 111 2 Sin Street Ibiza 999 5 12/06/2013 222 4 Bora Bora Bora Bora 1000 10 17/07/2013 444 

I tried with DISTINCT for all columns, but this will not work as the ID is unique and always returns all columns. Also tried Group by Street, City PostalCode ProjectID , but there was an error regarding Date and NameId .

 Column '' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

So how can I get a result where the subset of columns is different?

Answer 1

You want to use the row_number() function:

 select t.ID, t.Street, t.City, t.PostalCode, t.ProjectID, t.Date, t.NameId from (select t.*, row_number() over (partition by Street, City, PostalCode, ProjectId order by id ) as seqnum from t ) t where seqnum = 1; 

This is a window function that assigns a sequential value to rows with the same values ​​in certain columns (defined by the partition by clause). The order in these lines is determined by the order by clause. In this case, it starts ordering with the smallest id in the group, so the external query simply selects the first one.

Answer 2

you can use this query

 select myTable.* from (select myTable.*, row_number() over (partition by Street, City, PostalCode, Projected order by id ) as rowid from myTable ) myTable where rowid = 1; 

SQL Fiddle

Answer 3

You can also try the following query:

 select * from myTable where id in ( select min(id) from myTable group by Street, City, PostalCode,ProjectID )