Results 1 to 10 of 10

Thread: [RESOLVED] How to group with distinct

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    993

    Resolved [RESOLVED] How to group with distinct

    I have a table with 3M+ rows and there are two columns I want to generate a results for. Jdx is an integer and there are only about 150 distinct values. Exemptions is varchar and within the set of a given jdx, have multiple values. This results below are for Jdx 25, but I want them for all jdx.
    SQL Code:
    1. Select distinct(Exemptions), jdx from Accounts
    2. Where Jdx = 25
    Name:  2020-06-11_15-14-27.jpg
Views: 79
Size:  25.2 KB
    I've been trying Group By clauses but I never get it right. If I remove the Where clause, it doesn't work.
    SQL Code:
    1. Select distinct(Exemptions), jdx from Accounts
    2. Where Jdx in (select distinct Jdx from Accounts)
    3. Order by Jdx
    This works, but I have no idea why. It seems like it's no different than the first query without the Where criteria.

    Thank you for taking the time to read my post.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,950

    Re: How to group with distinct

    As far as I can tell, there is no grouping to be done. You simply want every unique pair of Exemption and jdx values. I would have thought that you would want something like this:
    sql Code:
    1. SELECT DISTINCT Exemptions, jdx
    2. FROM Accounts
    3. ORDER BY jdx
    Grouping would require some sort of aggregation, like MAX or COUNT.

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,404

    Re: How to group with distinct

    I would write it like this:
    Code:
    SELECT
        Exemptions
        ,jdx
        ,COUNT(*)
    FROM Accounts
    GROUP BY Exemptions,jdx
    ORDER BY jdx;
    The Group By will ensure that the value combinations are quinine
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,950

    Re: How to group with distinct

    Quote Originally Posted by GaryMazzone View Post
    The Group By will ensure that the value combinations are quinine
    What exactly does that word mean in that context?

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: How to group with distinct

    Lol, drinking Gin helps protect your query from malaria attacks.

    I suspect it was a mistyping of Unique
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,404

    Re: How to group with distinct

    I have a very hard time spelling anything... In fact one place I worked said they always knew it was me by the unique mis-spelling of things
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    993

    Re: How to group with distinct

    Quote Originally Posted by GaryMazzone View Post
    In fact one place I worked said they always knew it was me by the unique mis-spelling of things
    The FBI and other TLA agencies have software that creates a unique profile (fingerprint) based on written text. Just like ridges on fingers, people have a vocabulary of words they use often, style or writing, punctuation, and so forth. With this they can identify people posting in a forum anonymously, for instance, if they have other writings from elsewhere large enough to create a statistically distinct profile. I read once that one of the most useful parameters is frequency of misspelled words and common misspelled words.
    Make sure you use a spell checker if you write anonymously Gary! :-) JK

  8. #8

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    993

    Re: How to group with distinct

    John is so right. I was thinking of something like this on my walk this morning so I was excited when I saw his post. I didn't know how to do it yet, but it was what I was thinking. Somehow in my mind distinct was an aggregate function and I was wrong. I need to consider this more.
    I love the count in Gary's query. And that's interesting to see grouping used that way. After work I'm going to sit down and study that part of the book again. I keep learning things but then not applying them so I forget.
    Thanks to all of you.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: [RESOLVED] How to group with distinct

    in my mind distinct was an aggregate function
    It sort of is. Basically, distinct is just short hand for Grouping By every column.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    993

    Re: [RESOLVED] How to group with distinct

    Very interesting FunkyDexter. I like things like that. They help me understand better.

Posting Permissions

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



Click Here to Expand Forum to Full Width