simple sql statement.
Posted in 2000
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.
Topics: General Discussion
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.
>
>