SQL Server: How do I return all columns where one is not repeated?

Question

I've got a table with repeating IDs which I will fix later. Basically I want to return all the rows where the ID is distinct, but I want the ENTIRE row. Something like:

select * from table group by ID

select * from table where (ID is not repeated)

In this case, they are identical rows, so I don't care if it's First or Last, Min or Max.

Note that I DON"T want to do this:

select MIN(col1), MIN(col2), ... from table group by ID

I want a way to get this result without enumerating every column.

Edit: I'm using SQL Server 2008 R2.

Answer 1

If you are using MySql, do this:

select 
    *
from tbl
group by ID

MySQL live test: http://www.sqlfiddle.com/#!2/8c7fd/2

If you are using Postgresql, do this:

select distinct on(id)
    *
from tbl
order by id

If you want Postgresql DISTINCT ON be at least as predictable as CTE windowing function. Sort another column:

select distinct on(id)
    *
from tbl
order by id
   , someColumnHere -- Choose ASC for first row, DESC for last row

Postgresql live test: http://www.sqlfiddle.com/#!1/8c7fd/1

If you are using CTE windowing-capable database (e.g. Postgres, Oracle, Sql Server), use this:

with ranked as
(
  select 
      rank() over(partition by id order by column) rn,
      *
  from tbl
)
select * from ranked where rn = 1

CTE windowing-capable databases:

Posgtresql: http://www.sqlfiddle.com/#!1/8c7fd/2

Oracle: http://www.sqlfiddle.com/#!4/b5cf9/1

Sql Server: http://www.sqlfiddle.com/#!3/8c7fd/3

Answer 2

Since you didn't state in your question what database are you using, I suggest you make a query that will work on all database platforms. But this query requires you to make a new column with a property of auto_number,identity,serial,etc

This would be the query:

select * from tbl 
where (id,auto_number_here) in
   (select id, min(auto_number_here) 
    from tbl 
    group by id)

That will work on many platforms, except Sql Server. Sql Server is not tuple-capable. You have to do this:

select * from tbl x
where 
   -- leave this comment, so it mimics the tuple capability
   -- (id,auto_number_here) in
   EXISTS
   (select
       -- Replace this:  
       -- id, min(auto_number_here) 

       -- With whatever floats your boat, 
       -- you can use 1, null(the value generated by Entity Framework's EXIST clause), 
       -- even 1/0 is ok :-) (this will not result to divide-by-zero error)

       -- But I prefer retaining the columns, so it mimics the tuple-capable database:
       id, min(auto_number_here) 

    from tbl 
    where id = x.id 
    group by id
    having x.auto_number_here = min(auto_number_here))

Tuple-related question: using tuples in sql in clause

Since some database doesn't support tuple, you can simulate it instead

select z.* from tbl z
join (select id, min(auto_number_here) as first_row from tbl group by id) as x
on z.id = x.id and z.auto_number_here = x.first_row

It's a bit better than EXISTS approach. But if your database supports tuple use it instead; as much as possible, use JOIN for reflecting table relationships only, and use WHERE clause for filtering.

---

UPDATE

Perhaps a concrete example could explain it clearly, suppose we have existing table that we forgot to put a primary key on:

create table tbl(
  id varchar(5), -- supposedly primary key 
  data int,
  message varchar(100) 
);


insert into tbl values
('A',1,'the'),
('A',1,'quick'),
('A',4,'brown'),
('B',2, 'fox'),
('B',5, 'jumps'),
('B',5, 'over'),
('C',6, 'the'),
('C',7, 'lazy');

In order to pick up only one row out of duplicates, we need to add a third column on existing data.

This will aid us on picking up one and only one row out of duplicates

alter table tbl add auto_number_here int identity(1,1) not null;

This shall work now:

select z.* from tbl z
join (select id, min(auto_number_here) as first_row from tbl group by id) as x
on z.id = x.id and z.auto_number_here = x.first_row

Live test: http://www.sqlfiddle.com/#!6/19b55/3

And so is this:

select * from tbl x
where 
   -- leave this comment, so it mimics the tuple capability
   -- (id,auto_number_here) in
   EXISTS
   (
     select
       -- Replace this:  
       -- id, min(auto_number_here) 

       -- With whatever floats your boat, 
       -- you can use 1, null(the value generated by Entity Framework's EXIST clause), 
       -- even 1/0 is ok :-) (this will not result to divide-by-zero error)

       -- But I prefer retaining the columns, so it mimics the tuple-capable database:
       id, min(auto_number_here) 

    from tbl 
    where id = x.id 
    group by id
    having x.auto_number_here = min(auto_number_here)

   )

Live test: http://www.sqlfiddle.com/#!6/19b55/4

Answer 3

Try this out. It might work because all columns in the two rows are identical.

select distinct *
from table

Answer 4

Use a subquery to get your unique ids, then use that to filter the results:

SELECT *
FROM YourTable t,
INNER JOIN (
  SELECT Id, COUNT(*) 'count'
  FROM YourTable
  GROUP BY Id
) sq ON sq.Id = t.Id
WHERE sq.count = 1