simple sql statement.
Langton Tigere asked how to keep one row and delete duplicates where several records share the same ref. Phillip suggested DISTINCT. Norman Erickson Lugtu gave SQL using a temp table of max(ref) per key columns and a delete of lower rows. Bill Raper suggested SELECT FIRST 1 as an alternative where applicable. No confirmation of outcome.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Can anyone tell me how I can go about ,if my SQL syntax is returning more than one row and I wish it to return just a maximum of one row per given selection criteria.For example I have 3 records with same ref and every other field, I wish to use a syntax that queries that table by ref returning back only one record and deleting the other 2 records. please help
have you tried the DISTINCT keyword in your query?
select distinct <column_name> from <table_name> where <column_name> =<value>;
Langton.Tigere@icl.com wrote:
> Can anyone tell me how I can go about ,if my SQL syntax is returning more
> than one row and I wish it to return just a maximum
> of one row per given selection criteria.For example I have 3 records with
> same ref and every other field, I wish to use a syntax that queries that
> table
> by ref returning back only one record and deleting the other 2 records.
> please help
--
Phillip Tien
Database Administrator
Whole Foods Market, Inc.
It's not gonna be an orgy...it's a toga party.
Try this SQL:
select key1, key2, max(ref) maxref
from tab
group by key1, key2
into temp temp_tab;
delete from tab
where ref < (select maxref from temp_tab
where tab.key1 = temp_tab.key1 and
tab.key2 = temp_tab.key2);
In article <90lccg$ssj$1@news.xmission.com>,
Langton.Tigere@icl.com wrote:
>
> Can anyone tell me how I can go about ,if my SQL syntax is returning
more
> than one row and I wish it to return just a maximum
> of one row per given selection criteria.For example I have 3 records
with
> same ref and every other field, I wish to use a syntax that queries
that
> table
> by ref returning back only one record and deleting the other 2
records.
> please help
>
Sent via Deja.com http://www.deja.com/
Before you buy.
One other alternative (depends on specific SQL statement whether it will
work):
Select First 1 * from .....
"Phillip" <tienp@wholefood.com> wrote in message
news:3A2E7165.51BEE23A@wholefood.com...
> have you tried the DISTINCT keyword in your query?
>
> select distinct <column_name> from <table_name> where <column_name> => <value>;
>
> Langton.Tigere@icl.com wrote:
>
> > Can anyone tell me how I can go about ,if my SQL syntax is returning
more
> > than one row and I wish it to return just a maximum
> > of one row per given selection criteria.For example I have 3 records
with
> > same ref and every other field, I wish to use a syntax that queries that
> > table
> > by ref returning back only one record and deleting the other 2 records.
> > please help
>
> --
> Phillip Tien
> Database Administrator
> Whole Foods Market, Inc.
>
> It's not gonna be an orgy...it's a toga party.
>
>