Results 1 to 7 of 7

Thread: group by woe

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

    Unanswered: group by woe

    hi, I don't seem to be able to figure out a better solution for this problem in SQL. Basically I want to display all the records have certain duplicate fields. In SQL, it's something like

    select * from x where (b,c) in
    (select b,c,count(*)
    from
    x
    group by b,c
    having count(*) > 1);

    But the above is illegal because of the count(*) in returned result set. Any suggestions?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    try:

    select *
    from x, (select b,c,count(*)
    from x
    group by b,c
    having count(*) > 1) inline_view1
    where x.b = inline_view.b and
    x.c = inline_view.c

    -cf

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think it's is much simpler than you imagine...

    PHP Code:
    select b,c,count(*)
    from
    x
    group by b
    ,c
    having count
    (*) > 
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You could use the IN operator if you wanted to retrieve records that contained duplicate values for a subset of the table's atributes. Specifically, if you wanted to display all attributes of the violating records.

    Example:

    Select columnA, columnB, columnC, columnD, columnE
    from table
    where (columnC, columnD) IN
    (Select columnC, columnD)
    from table
    group by columnC, columnD
    having count(*) > 1);
    Last edited by r123456; 03-08-04 at 22:37.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    but IN operator ignores the null values. for example, i have a lot of records having columnD null. They appears in the inner select result, not the final result.

    Yeah, I didn't know you can use count(*) implicitly in a group by statement. So it is easier than I thought.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes, somehow I managed to miss the actual question in your original post, so my suggestion wasn't exactly useful. I did not mean to offend.

    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    Hi, Thanks for the inline_view tip. But it suffers the same problem as the other one. It will ignore all the null columns.
    But it's a relatively easier fix in the where clause:

    where (x.b = inline_view.b and
    x.c = inline_view.c)
    or (x.b = inline_view.b and
    x.c is null and
    inline_view.c)


    Originally posted by chuck_forbes
    try:

    select *
    from x, (select b,c,count(*)
    from x
    group by b,c
    having count(*) > 1) inline_view1
    where x.b = inline_view.b and
    x.c = inline_view.c

    -cf

Posting Permissions

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