Results 1 to 8 of 8

Thread: I hate SQL

  1. #1
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41

    Unanswered: I hate SQL

    cannot believe this following is so difficult to perform:

    I have a table:

    PK A B C
    1 1 null 1
    2 1 null 2
    3 2 2 3
    4 3 3 4
    5 3 3 5

    And I want to get the list of all pk's having duplication on Column A and B. In the above case, the return set would be 1, 2, 4, 5.

    I tried almost everything: inline_view, group by, in, not in, but the null is killing me.

    Help!

  2. #2
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    And suppose you have 15 columns that could have nulls and you want to group by on them.

    I think I am screwed!

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    do you mean where colA = colB??

    OR do you mean where line1 PK, A, B = line2 PK, A, B??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Not every problem can be solved using SQL.

  5. #5
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    Originally posted by The_Duck
    do you mean where colA = colB??

    OR do you mean where line1 PK, A, B = line2 PK, A, B??
    I mean line1 A, B = line2 A, B. PK stands for primary key.

  6. #6
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Re: I hate SQL

    [QUOTE][SIZE=1]Originally posted by mongoloid001
    >cannot believe this following is so difficult to perform:

    >I have a table:

    >PK A B C
    >1 1 null 1
    >2 1 null 2
    >3 2 2 3
    >4 3 3 4
    >5 3 3 5

    >And I want to get the list of all pk's having duplication on Column A and B. In the above case, the return set would be 1, 2, 4, 5.

    >I tried almost everything: inline_view, group by, in, not in, but the null is killing me.

    How about (although I don't like the null) :-
    SELECT a,nvl(b,some_value_that_doesnt_occur_naturally),CO UNT(*)
    FROM table
    GROUP BY a,b
    HAVING COUNT(*)>1

    Good luck.

    Barry

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    this is what you want:
    PHP Code:
    platform@kod1create table test (
      
    2   col1 varchar2(4),
      
    3   col2 varchar2(4),
      
    4   col3 varchar2(4),
      
    5   col4 varchar2(4));

    Table created.

    platform@kod1>  
    platform@kod1insert into test values('1','1',null,'1');

    1 row created.

    platform@kod1insert into test values('2','1',null,'2');

    1 row created.

    platform@kod1insert into test values('3','2','2','3');

    1 row created.

    platform@kod1insert into test values('4','3','3','4');

    1 row created.

    platform@kod1insert into test values('5','3','3','5');

    1 row created.

    platform@kod1
    platform@kod1select from test;

    COL1 COL2 COL3 COL4
    ==== ==== ==== ====
    1    1         1
    2    1         2
    3    2    2    3
    4    3    3    4
    5    3    3    5

    platform
    @kod1
    platform@kod1select col1 from (
      
    2  select 
      3  count
    (*) over (partition by col2col3amount,
      
    4  t.*
      
    5  from test t)
      
    6  where amount 1;

    COL1
    ====
    1
    2
    4

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    oh, geez! beautiful. How do I change the subject line now

    Thanks a million!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •