Thread: selecting rows based on unique fields in a column

Hi,
    Is there an easy way to select all rows which have a single entry in a
particular column from those that have multiple entries.

At present I'm using

   select reqitem from lasting
group by reqitem having count(reqitem) = 1;

But this gives me only reqitem column and I want the rest of the row!

I can see how to do this by creating another table updating it with the
above and then selcting into the table all the rest of the row, but is
there an easier way, have I missed something obvious?

Thanks for any response.

John S

On Wed, 05 Nov 2003 11:52:30 +0000, John Siggins
<j-siggins@blueyonder.co.uk> wrote:
>   select reqitem from lasting
>group by reqitem having count(reqitem) = 1;
>
>But this gives me only reqitem column and I want the rest of the row!

SELECT l.*
  FROM lasting AS l INNER JOIN (
       SELECT reqitem FROM lasting
        GROUP BY reqitem HAVING count(reqitem) = 1
       ) AS x ON l.reqitem = x.requitem;

Servus
 Manfred

Manfred Koizar wrote:
> On Wed, 05 Nov 2003 11:52:30 +0000, John Siggins
> <j-siggins@blueyonder.co.uk> wrote:
>
>>  select reqitem from lasting
>>group by reqitem having count(reqitem) = 1;
>>
>>But this gives me only reqitem column and I want the rest of the row!
>
>
> SELECT l.*
>   FROM lasting AS l INNER JOIN (
>        SELECT reqitem FROM lasting
>         GROUP BY reqitem HAVING count(reqitem) = 1
>        ) AS x ON l.reqitem = x.requitem;
>
> Servus
>  Manfred
Thanks v much
    This seems to works a treat. Your method and Michael's return the same
no of rows so both methods look good to me.

John