Re: Beginner SQL question

From: <Markz_at_starnet.lenfest.com>
Date: Fri, 12 Mar 1999 20:01:27 GMT
Message-ID: <36e968fc.171482348@news1.fast.net>

SELECT * FROM a_table t1
WHERE t1.ROWID = (

                                           SELECT MIN(t2.ROWID) 
                                           FROM a_table t2
                                           WHERE t1.ssn = t2.ssn
                                           )

AND address IS NULL;

ROWID is a pseudo column representing the physical location of a logical record. It is guaranteed unique as obviously no two records can share the same physical location.

Another way, with a little overhead noise would be to use group by to *unique* the rows
SELECT ssn, count(*) FROM a_table
WHERE address IS NULL
group by SSN

Then ignore the count column, or if using sqlplus use: COLUMN count(*) NOPRINT
and the count(*) column will not be displayed until you : CLEAR COLUMNS On Fri, 05 Mar 1999 21:43:56 GMT, "rmaggio" <rmaggio_at_courts.state.ny.us> wrote:

>I am trying to retrieve all records that contain NULL in a paticular field
>and from those, only a single occurance of a SSN. Somewhat like:
> Select * from Table where addresss = NULL
>and ( select * from that resultset where their SSN is not repeated)
> So out of the ones that have nothing in a feild, I only want a single
>occurance of the record based on their SSN. (i.e Two records for customer
>123-45-6789 only show up once) Thanks for your help
>Bob
> rmaggio_at_courts.state.ny.us
>or
>zekmoe_at_aol.com

+-+-++-+-++-+-++-+-++-+-++-+-++-+-++-+-+ Lenfest Communications
West Chester, PA.

My opinions are not neccessarily those of my employer - but probably should be ! Received on Fri Mar 12 1999 - 14:01:27 CST