SQL Server select distinct hell

Question

Hi there,

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.

Answer 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

Answer 2

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