Results 1 to 10 of 10
  1. #1

    Default Reverse of 'distinct' in sql


    Mga brod expert sa MySQL, what is the reverse of distinct?

    Sample input
    AAA
    BBB
    CCC
    CCC
    DDD
    AAA
    AAA

    Output:
    AAA
    CCC

    I got a solution but I'm not satisfied with performance coz I'm using sub select & I got lots of records, what is your solution to this?

  2. #2
    I think CARTESIAN PRODUCT perhaps i'm not sure which part you meant is. That's related to reduntant results on SQL queries.


    It's more likely that you should combine DISTINCT query and WHERE statement with using combine
    like '%something%' or '_something_'

    If it is under a GROUP or related contents, try use WHERE agains it.

    If it doesn't work, Find a nother way and querying and rely on it's Foreign Keys.

  3. #3
    @javapenguin .. wa ko ka gets by default DISTINCT results would be

    AAA
    BBB
    CCC
    DDD

    right? however you only want these 2 records?

    AAA
    CCC

    to clarify things up, you only need to display records having 2 or more occurrences?
    Last edited by BadDudes; 01-30-2009 at 04:48 PM.
    ALONE:Hated and Punished

  4. #4
    Quote Originally Posted by BadDudes View Post
    @javapenguin .. wa ko ka gets by default DISTINCT results would be

    AAA
    BBB
    CCC
    DDD

    right? however you only want these 2 records?

    AAA
    CCC

    to clarify things up, you only need to display records having 2 or more occurrences?
    You are correct, I only need records having 2 or more occurrences, those that are unique must not be displayed, so I need something that is the reverse of DISTINCT. I got a bad solution and I'm looking for a better one, preferably no sub select.

  5. #5
    Not sure on this one

    Code:
    SELECT field1, COUNT(*)
     FROM table1
     GROUP BY field1
     HAVING COUNT(*) > 1
    if you have to use order by it comes after Having keyword

  6. #6
    Quote Originally Posted by moz_k2 View Post
    Not sure on this one

    Code:
    SELECT field1, COUNT(*)
     FROM table1
     GROUP BY field1
     HAVING COUNT(*) > 1
    if you have to use order by it comes after Having keyword
    Yes! This will surely work! I keep thinking what I've been missing. It was the HAVING keyword.

    Thanks a lot!

  7. #7
    Quote Originally Posted by javapenguin View Post
    Yes! This will surely work! I keep thinking what I've been missing. It was the HAVING keyword.

    Thanks a lot!
    Yeheyyyyy!!!! always pair the HAVING and GROUP.
    HAVING might not work without the GROUP magic word

  8. #8
    Quote Originally Posted by moz_k2 View Post
    Not sure on this one

    Code:
    SELECT field1, COUNT(*)
     FROM table1
     GROUP BY field1
     HAVING COUNT(*) > 1
    if you have to use order by it comes after Having keyword
    definitely this would be your solution
    ALONE:Hated and Punished

  9. #9
    The solution is correct but the question is incorrect. The solution is not the reverse or more appropriately saying inverse of DISTINCT.

    Let it be written that WHERE clause filters the rows, then the remaining rows are grouped into blocks by the GROUP BY clause, finally the row groups are filtered by the HAVING clause. This enters the solution, HAVING clause to filter the row groups to those greater than one (>1) and that makes the solution correct.

    On the other hand, simply remove the DISTINCT in the SELECT statement and that's the inverse of DISTINCT but surely that's not the solution.

  10. #10
    Quote Originally Posted by dlanyer77 View Post
    The solution is correct but the question is incorrect. The solution is not the reverse or more appropriately saying inverse of DISTINCT.

    Let it be written that WHERE clause filters the rows, then the remaining rows are grouped into blocks by the GROUP BY clause, finally the row groups are filtered by the HAVING clause. This enters the solution, HAVING clause to filter the row groups to those greater than one (>1) and that makes the solution correct.

    On the other hand, simply remove the DISTINCT in the SELECT statement and that's the inverse of DISTINCT but surely that's not the solution.
    Good point, thanks for the correction

  11.    Advertisement

Similar Threads

 
  1. Replies: 47
    Last Post: 11-15-2011, 04:02 PM
  2. Replies: 68
    Last Post: 02-02-2009, 02:59 PM
  3. Replies: 8
    Last Post: 04-26-2008, 03:37 PM
  4. What is the role of women in nation-buliding?
    By neishan731 in forum Politics & Current Events
    Replies: 8
    Last Post: 06-07-2006, 07:02 AM
  5. Replies: 17
    Last Post: 01-12-2006, 11:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
about us
We are the first Cebu Online Media.

iSTORYA.NET is Cebu's Biggest, Southern Philippines' Most Active, and the Philippines' Strongest Online Community!
follow us
#top