SELECT DISTINCT...

[ejaboneta]

This is driving me crazy... I'm used to mysql so I can't figure out how to get multiple fields with a distinct field...

 

This is what I'm trying to do...

SELECT DISTINCT client, producer, submitdate FROM clientTable

 

What I want to happen is to get distinct rows for each client, with the details on who the agent is and what the submitted date was. Instead I get all rows because the distinction is on all three fields and not just client. How can I make this work?

[Brian Swan]

You are right about DISTINCT applying to all three fields, but I'm having trouble understanding what data you want. (What if a client has multiple entries for a producer, or with different submitdates? Do you only want one of those rows? Which one?) I'm probably just missing something. Can you share some example data from clientTable and the result set you'd like to see? I'm sure there is a way...just need to better understand the scenario.

 

Thanks.

 

-Brian

[nealgseattle]

It would also be helpful to see the comparable MySQL code.  DISTINCT in Transact-SQL is designed to return only one copy of duplicate rows.  If you don't have duplicate rows in your table, which you most likely won't have in a normalized database, you will get back all rows in the table.

[nealgseattle]

Actually, let me be a little more accurate:  DISTINCT is designed to return a result set that eliminates duplicate rows based on the columns in the SELECT list, not necessarily in the table.  Sorry about that.

[ejaboneta]

The ultimate goal is to return a count of all the new clients for each of producer during a given period of time. For example, Bob had 20 new clients for the month of September. I want to do that by querying a View of the first submitted policy for each client. That is what this is for.

 

What I'm trying to do with this is get the client, producer and submitted date for the first policy of each client. I'm sure I can figure out the rest of the query if I could only get the (column only) DISTINCT clients along with the producer and submit date.

So to make it simple... Yes, I want distinct 'clients' with the producer and submit date of one row for each.

 

[nealgseattle]

I think I have a solution, if I understand correctly.  I'll set up a set of sample data, which I will then query.  I'm using the Query Analyzer in SQL Server Management Studio for running the code in the sample.

 

First, I'll set up a table:

CREATE TABLE clienttable

    (

    client VARCHAR(25),

    producer VARCHAR(25),

    submitdate INT

    );

 

Then, I'll populate the table with fictitious data:

INSERT INTO clienttable

    VALUES

        ('Ceres','Zeus',20100115),

        ('Hespera','Athena',20100221),

        ('Ceres','Zeus',20100102),

        ('Juno','Poseiden',20100105),

        ('Jupiter','Apollo',20100316),

        ('Minerva','Hestia',20100225),

        ('Hespera','Athena',20100325),

        ('Nemesis','Vesta',20100310),

        ('Neptune','Hera',20100111),

        ('Neptune','Hestia',20100212),

        ('Jupiter','Apollo',20100320),

        ('Hespera','Athena',20100226);

 

I need to get a subset of the data from this table based on the first time a client is entered.  To get that client entry record, I run this query:

SELECT

        client,

        MIN(submitdate) AS firstPolicy

    FROM clienttable

    GROUP BY client;

 

And I get 7 rows:

    client                    firstPolicy

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

    Ceres                    20100102

    Hespera                  20100221

    Juno                      20100105

    Jupiter                  20100316

    Minerva                  20100225

    Nemesis                  20100310

    Neptune                  20100111

 

Then, I use this query to join against the actual table in order to get the producer - I'm joining a subset of the table to the table itself:

SELECT

        T2.client,

        T2.firstPolicy,

        T1.producer

    FROM clienttable  T1

    INNER JOIN

        (

        SELECT

                client,

                MIN(submitdate) AS firstPolicy

                FROM clienttable

                GROUP BY client

        ) AS T2

        ON T1.client = T2.client

            AND T1.submitdate = T2.firstPolicy;

 

And this give me this result:

    client                    firstPolicy producer

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

    Ceres                    20100102    Zeus

    Hespera                  20100221    Athena

    Juno                      20100105    Poseiden

    Jupiter                  20100316    Apollo

    Minerva                  20100225    Hestia

    Nemesis                  20100310    Vesta

    Neptune                  20100111    Hera

 

Is this what you're looking for?

[ejaboneta]

Yes! Thank you.