SQL Server select distinct hell

开发者_如何学编程I'm stuck on a problem with sql. I have a table with many duplicate entries with column names like:-

eventnumber housenumber value1 value2

None of these column names are the primary key as there are many duplicates. What I would like to do is select into another table by distinct housenumber but I seem to get the whole table copied across, I'm using:-

Select * into temp_table from schedule_temp where housenumber in (select distinct housenumerb from schedule_temp)

Now I broke it down a bit and if I do:-

Select distinct housenumber into temp from schedule_temp group by housenumber

I get a table with the unique housenumbers... but then how could I use this unique table to drive another select that picks housenumbers from temp and only gets one instance of the housenumber from schedule_temp? Hope that makes sense.

Beers are on me if you can save my sanity.

You don't want DISTINCT records (as in, each record has at least one column different than all other records). You want a winner from each group (or partition) of records.

SELECT *
FROM
(
  SELECT
    EventNumber, HouseNumber, Value1, Value2,
    ROW_NUMBER()
      OVER(PARTITION BY HouseNumber ORDER BY HouseNumber) as rowNum
  FROM ServiceAddr
) sub
WHERE sub.rowNum = 1

First I'd get the data into a table with an auto increment id

So create a table with id,eventnumber,housenumber,value1, value2 where id is an auto number.

Then

Insert Into NewTemp(eventnumber,housenumber,value1, value2)
Select eventnumber,housenumber,value1, value2 From schedule_temp

Then this query should guve you 1 row per house #

Select nt.* From NewTemp nt
Join (select max(id) as id, housenumber from NewTemp Group By housenumber) t on t.id=nt.id