One Unique Field

16 Nov 2006 8:48 PM

anthony

I have a table with 4 fields, that can contain duplicates.
I have to build a new table where field3 is the primary key, no
duplicates.

How do I write the SQL to return all the rows only were filed3 is
unique?

Thanks
AJS

17 Nov 2006 1:53 AM

Dan Guzman

> How do I write the SQL to return all the rows only were filed3 is
> unique?

One method:

SELECT col1, col2, col3, col4
FROM dbo.MyTable
WHERE col3 IN
(
    SELECT col3
    FROM dbo.MyTable
    GROUP BY col3
    HAVING COUNT(*) = 1
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<anth***@station5.net> wrote in message

Show quoteHide quote

news:1163710137.747671.280270@m7g2000cwm.googlegroups.com...
>I have a table with 4 fields, that can contain duplicates.
> I have to build a new table where field3 is the primary key, no
> duplicates.
>
> How do I write the SQL to return all the rows only were filed3 is
> unique?
>
> Thanks
> AJS
>

17 Nov 2006 9:49 PM

anthony

Thanks, It does help.

Dan Guzman wrote:
Show quoteHide quote

> > How do I write the SQL to return all the rows only were filed3 is
> > unique?
>
> One method:
>
> SELECT col1, col2, col3, col4
> FROM dbo.MyTable
> WHERE col3 IN
> (
>     SELECT col3
>     FROM dbo.MyTable
>     GROUP BY col3
>     HAVING COUNT(*) = 1
> )
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> <anth***@station5.net> wrote in message
> news:1163710137.747671.280270@m7g2000cwm.googlegroups.com...
> >I have a table with 4 fields, that can contain duplicates.
> > I have to build a new table where field3 is the primary key, no
> > duplicates.
> >
> > How do I write the SQL to return all the rows only were filed3 is
> > unique?
> >
> > Thanks
> > AJS
> >