Re: Eliminating Duplicates in a table using SQL

From: RaviShankar <pc_at_TULBLR.UNISYS.COM>
Date: Fri, 19 Jan 1996 16:28:45 +0500
Message-Id: <9601191104.AA00022@alice.jcc.com>

>
> Hi everyone,
> I'm creating a view off of an existing table. My table has 3 columns: item,
> location, and subordinate item. One item at one location can have multiple
> subordinate items. In creating my view, I want to select only the first
> instance that I come across i.e. I want to exclude more than one row with
> the same values for item and location. I really want to keep the value of
> the first subordinate item intact, so I don't want to select item, location,
> min(subordinate) group by item, location. Besides, subordinate item is of
> type CHAR. Any suggestions?
>

---

Hi,

        If i have understood ur problem correctly this will the solution ...

SQL>select * from x;

      ITEM L SU
---------- - --

        10 A S0
        20 A S1
        30 C S5
        20 A S2
        30 C S6
        40 B S3
        40 B S4
        30 C S7

8 rows selected.

SQL> select * from x where rowid in (
  2 select min(rowid) from x group by item,loc);

      ITEM L SU
---------- - --

        10 A S0
        20 A S1
        30 C S5
        40 B S3

---

Hope this helps...

P.C. Ravi Shankar
Tata Unisys Ltd.
India. Received on Fri Jan 19 1996 - 06:04:54 CST