Tough SQL question! Need SQL Guru!

Tough SQL question! Need SQL Guru!

Post by Kevin Lark » Sat, 01 Aug 1998 04:00:00



Is it possible to write a SQL delete statement to delete occurences of
excess nonunique rows from a table based on the value of a column
(i.e. a key),

WITHOUT USING TEMPORARY TABLES OR CURSORS!
------------------------------------------

delete mytable
where ...

Here's what I mean:

Say, for simplicity's sake, that I have a table with only one
column.  In this column, I have rows populated as follows:

col                                   col
___                                   ___

1      I want to end up with this ->  1
2                                     2
2                                     3
3
3
3

In a nutshell, I want to remove any occurrences of tuples beyond the
first which have duplicated values in a given attribute.

I would really appreciate, if possible, the answer in ANSI SQL, but
failing that, Transact SQL would be great too.

 
 
 

Tough SQL question! Need SQL Guru!

Post by Anthony Mandi » Sat, 01 Aug 1998 04:00:00



> Is it possible to write a SQL delete statement to delete occurences of
> excess nonunique rows from a table based on the value of a column
> (i.e. a key),
...
> In a nutshell, I want to remove any occurrences of tuples beyond the
> first which have duplicated values in a given attribute.

> I would really appreciate, if possible, the answer in ANSI SQL, but
> failing that, Transact SQL would be great too.

        Well, if T-SQL is good enough then just try "create clustered
        index XXX on YYY(ZZZ) with ignore_dup_row". Not sure if this
        will work on MS SQL SWERVER but it will discard dup rows in
        Sybase, provided you understand the caveats - must be a
        non-unique clustered index and no unique index must already
        exist.

-am

 
 
 

Tough SQL question! Need SQL Guru!

Post by Russell Smi » Sat, 01 Aug 1998 04:00:00


This is a tough one. The way I would approach it would be to use two
columns, not one (col, isold in this example).
Now default all the isold values to "Y"
eg.

col     isold
1       Y
2       Y
2       Y
3       Y
3       Y
3       Y

Now insert the records you want to be left with, setting the isold
column to 'N' using an SQL statement something like this.

insert into mytable (col,isold)
select distinct col, 'N' from mytable

The table should now look like this,

col     isold
1       Y
2       Y
2       Y
3       Y
3       Y
3       Y
1       N
2       N
3       N
Finally,
delete mytable where isold='Y'
should leave you with the required data set.

col     isold
1       N
2       N
3       N

Quote:>Is it possible to write a SQL delete statement to delete occurences of
>excess nonunique rows from a table based on the value of a column
>(i.e. a key),

>WITHOUT USING TEMPORARY TABLES OR CURSORS!
>------------------------------------------

>delete mytable
>where ...

>Here's what I mean:

>Say, for simplicity's sake, that I have a table with only one
>column.  In this column, I have rows populated as follows:

>col                                   col
>___                                   ___

>1      I want to end up with this ->  1
>2                                     2
>2                                     3
>3
>3
>3

>In a nutshell, I want to remove any occurrences of tuples beyond the
>first which have duplicated values in a given attribute.

>I would really appreciate, if possible, the answer in ANSI SQL, but
>failing that, Transact SQL would be great too.

 
 
 

Tough SQL question! Need SQL Guru!

Post by Philip Lijnzaa » Sat, 01 Aug 1998 04:00:00


Here's another solution (although Russell's is more elegant):

delete from tab a
where a.rowid <>
        (select max(b.rowid) from tab b  where a.col = b.col);

This requires that you can refer to the 'underlying' id of a row. Many DBMSs
have this provision, in Oracle it's called rowid.

                                                                      Philip

--

-----------------------------------------------------------------------------

+44 (0)1223 49 4639                 | Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           | Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53

 
 
 

Tough SQL question! Need SQL Guru!

Post by ML from Dove » Mon, 03 Aug 1998 04:00:00


delete from mytable where rowid not in ( select min( rowid ) from mytable
group by col )

If you want to keep the last occurrence, then change the MIN aggregate
function to MAX in the subquery.


>Is it possible to write a SQL delete statement to delete occurences of
>excess nonunique rows from a table based on the value of a column
>(i.e. a key),

>WITHOUT USING TEMPORARY TABLES OR CURSORS!
>------------------------------------------

>delete mytable
>where ...

>Here's what I mean:

>Say, for simplicity's sake, that I have a table with only one
>column.  In this column, I have rows populated as follows:

>col                                   col
>___                                   ___

>1      I want to end up with this ->  1
>2                                     2
>2                                     3
>3
>3
>3

>In a nutshell, I want to remove any occurrences of tuples beyond the
>first which have duplicated values in a given attribute.

>I would really appreciate, if possible, the answer in ANSI SQL, but
>failing that, Transact SQL would be great too.

 
 
 

Tough SQL question! Need SQL Guru!

Post by Baolin Re » Wed, 05 Aug 1998 04:00:00


This one is for ORACLE.

Baolin


>Here's another solution (although Russell's is more elegant):

>delete from tab a
>where a.rowid <>
> (select max(b.rowid) from tab b  where a.col = b.col);

>This requires that you can refer to the 'underlying' id of a row. Many
DBMSs
>have this provision, in Oracle it's called rowid.

Philip

>--

>---------------------------------------------------------------------------
--

>+44 (0)1223 49 4639                 | Wellcome Trust Genome Campus, Hinxton
>+44 (0)1223 49 4468 (fax)           | Cambridgeshire CB10 1SD,  GREAT
BRITAIN
>PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53

 
 
 

1. Tough SQL question! Need SQL Guru!

Is it possible to write a SQL delete statement to delete occurences of
excess nonunique rows from a table based on the value of a column
(i.e. a key),

WITHOUT USING TEMPORARY TABLES OR CURSORS!
------------------------------------------

delete mytable
where ...

Here's what I mean:

Say, for simplicity's sake, that I have a table with only one
column.  In this column, I have rows populated as follows:

col                                   col
___                                   ___

1      I want to end up with this ->  1
2                                     2
2                                     3
3
3
3

In a nutshell, I want to remove any occurrences of tuples beyond the
first which have duplicated values in a given attribute.

I would really appreciate, if possible, the answer in ANSI SQL, but
failing that, Transact SQL would be great too.

2. Advice on picking a database (Oracle, Sybase, Informix, SQL Server

3. sqlsp3 terminal services

4. Here's a tough question for you SQL gurus

5. is there any way to speed up this sql ?

6. Need SQL Server Guru Help! [BCP Question]

7. Q: Minor SQL Guru seeking Major SQL Guru !!

8. ORACLE-SQL : Sequence Problem (Need SQL GURUS)