Re: How to do this in SQL?

From: Edmund Landgraf <elandgraf_at_nospam.1rent.com>
Date: Fri, 20 Aug 1999 09:47:38 -0700
Message-ID: <rrr1b11iboh33@corp.supernews.com>

Hi Peter,

It's not trivially to remove dups... the quickest way is to make a temp table with the ID's you want to keep (usinga a group by and having) then do the delete where ID is not in temp table.

Here's one I just wrote... the address table has addressid (PK) and three fields; street1, cityid, and addresstypeid. I wanted to delete the rows where the last three (mentioned) fields are dups.

Hope that helps a little.

-Ed

select a.addressid from address a where a.addressid not in (select distinct b.addressid from
( select min(a1.addressID) as AddressID   from address a2, address a1
  where a2.street1 = a1.street1 and

     a2.addresstypeid = a1.addresstypeid and
     a2.cityid = a1.cityid
     group by
     a2.addressid      having count(a2.addressid) > 1
     order by    a2.addressid) b

) order by a.addressid

Eric Dantie <edantie_at_rcanaria.es> wrote in message news:8E288DDD1edantiercanariaes_at_news.rcanaria.es...
> I've got a table history(login varchar2(30), d date)
>
> Each time someone connect to me, I do a insert into history(loginvalue,
> sysdate);
>
> I need the 10 lasts connections for the login 'toto'.
>
> How can I do this?
>
> Thanks in advance.
>
> Eric
Received on Fri Aug 20 1999 - 11:47:38 CDT