Professional Web Applications Themes

Select Distinct - Microsoft SQL / MS SQL Server

I feel a little stupid sending this, but I must be having a mental block or something. I am trying to do a Select Distinct on a column from a table, but keep getting back multiple results. Below is my query and sample results. QUERY: Select distinct(aip.policynumber) as PolicyNumber from accounts a join accountinsurancepolicies aip on aip.accountid = a.id where a.bsuspended = 1 group by aip.policynumber RESULTS: PolicyNumber ABC1234 XYX9876 In the accountinsurancepolicies there are multiple rows with the same account number, I only want to return one. What am i missing here? Thanks in advance for your help....

  1. #1

    Default Select Distinct

    I feel a little stupid sending this, but I must be having a mental block or
    something. I am trying to do a Select Distinct on a column from a table,
    but keep getting back multiple results. Below is my query and sample
    results.

    QUERY:
    Select distinct(aip.policynumber) as PolicyNumber
    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    where a.bsuspended = 1
    group by aip.policynumber

    RESULTS:
    PolicyNumber
    ABC1234
    XYX9876

    In the accountinsurancepolicies there are multiple rows with the same
    account number, I only want to return one. What am i missing here?

    Thanks in advance for your help.


    Scott Guest

  2. #2

    Default Re: Select Distinct

    The resultset you've shown is giving you the distinct result set, are these
    values repeating.?
    Try:
    Select distinct rtrim(ltrim(aip.policynumber)) as PolicyNumber
    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    where a.bsuspended = 1

    --to find out whether there are really muliple values you can run query like
    this.


    Select count(*) cnt,aip.policynumber as PolicyNumber
    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    where a.bsuspended = 1
    group by aip.policynumber
    having count(*) > 1


    --
    -Vishal
    "Scott" <smorgansiuins.com> wrote in message
    news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > I feel a little stupid sending this, but I must be having a mental block
    or
    > something. I am trying to do a Select Distinct on a column from a table,
    > but keep getting back multiple results. Below is my query and sample
    > results.
    >
    > QUERY:
    > Select distinct(aip.policynumber) as PolicyNumber
    > from accounts a
    > join accountinsurancepolicies aip on aip.accountid = a.id
    > where a.bsuspended = 1
    > group by aip.policynumber
    >
    > RESULTS:
    > PolicyNumber
    > ABC1234
    > XYX9876
    >
    > In the accountinsurancepolicies there are multiple rows with the same
    > account number, I only want to return one. What am i missing here?
    >
    > Thanks in advance for your help.
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Select Distinct

    Sorry that did not fix it. I ran it without the group by and get the same
    results
    "Jason" <Jason.Lipmanstate.sd.us> wrote in message
    news:u8EK$QWQDHA.704tk2msftngp13.phx.gbl...
    > Remove the Group by. If you want to order, use Order By. See if this
    solves
    > your problem. :)
    >
    > Jason
    >
    > "Scott" <smorgansiuins.com> wrote in message
    > news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > I feel a little stupid sending this, but I must be having a mental block
    > or
    > > something. I am trying to do a Select Distinct on a column from a
    table,
    > > but keep getting back multiple results. Below is my query and sample
    > > results.
    > >
    > > QUERY:
    > > Select distinct(aip.policynumber) as PolicyNumber
    > > from accounts a
    > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > where a.bsuspended = 1
    > > group by aip.policynumber
    > >
    > > RESULTS:
    > > PolicyNumber
    > > ABC1234
    > > XYX9876
    > >
    > > In the accountinsurancepolicies there are multiple rows with the same
    > > account number, I only want to return one. What am i missing here?
    > >
    > > Thanks in advance for your help.
    > >
    > >
    >
    >

    Scott Guest

  4. #4

    Default Select Distinct



    the results are distinct??


    >-----Original Message-----
    >I feel a little stupid sending this, but I must be having
    a mental block or
    >something. I am trying to do a Select Distinct on a
    column from a table,
    >but keep getting back multiple results. Below is my
    query and sample
    >results.
    >
    >QUERY:
    >Select distinct(aip.policynumber) as PolicyNumber
    > from accounts a
    > join accountinsurancepolicies aip on
    aip.accountid = a.id
    >where a.bsuspended = 1
    >group by aip.policynumber
    >
    >RESULTS:
    >PolicyNumber
    >ABC1234
    >XYX9876
    >
    >In the accountinsurancepolicies there are multiple rows
    with the same
    >account number, I only want to return one. What am i
    missing here?
    >
    >Thanks in advance for your help.
    >
    >
    >.
    >
    Matt h Guest

  5. #5

    Default Re: Select Distinct

    Now that the light bulb went off, I realize my query was not right. What I
    need is a distinct record from the accountinsurancepolicies table that has
    the same account Id that is in the account table. EX:

    ACCOUNT TABLE:
    ID - 3
    Amount: 100.00

    ACCOUNTINSURANCEPOLICIES TABLE:
    ACCOUNTID - 3
    POLICY: ABC123

    ACCOUNTID - 3
    POLICY: XYZ9876

    The Join is on ACCOUNTID = ID (from the account table)

    What I need is the record with the policy ABC123.

    Make sense?
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:urvixNWQDHA.3700tk2msftngp13.phx.gbl...
    > The resultset you've shown is giving you the distinct result set, are
    these
    > values repeating.?
    > Try:
    > Select distinct rtrim(ltrim(aip.policynumber)) as PolicyNumber
    > from accounts a
    > join accountinsurancepolicies aip on aip.accountid = a.id
    > where a.bsuspended = 1
    >
    > --to find out whether there are really muliple values you can run query
    like
    > this.
    >
    >
    > Select count(*) cnt,aip.policynumber as PolicyNumber
    > from accounts a
    > join accountinsurancepolicies aip on aip.accountid = a.id
    > where a.bsuspended = 1
    > group by aip.policynumber
    > having count(*) > 1
    >
    >
    > --
    > -Vishal
    > "Scott" <smorgansiuins.com> wrote in message
    > news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > I feel a little stupid sending this, but I must be having a mental block
    > or
    > > something. I am trying to do a Select Distinct on a column from a
    table,
    > > but keep getting back multiple results. Below is my query and sample
    > > results.
    > >
    > > QUERY:
    > > Select distinct(aip.policynumber) as PolicyNumber
    > > from accounts a
    > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > where a.bsuspended = 1
    > > group by aip.policynumber
    > >
    > > RESULTS:
    > > PolicyNumber
    > > ABC1234
    > > XYX9876
    > >
    > > In the accountinsurancepolicies there are multiple rows with the same
    > > account number, I only want to return one. What am i missing here?
    > >
    > > Thanks in advance for your help.
    > >
    > >
    >
    >

    Scott Guest

  6. #6

    Default Re: Select Distinct

    Is there not a way to pull just one record from the ACCOUNTINSURANCEPOLICIES
    Table?
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:OARHkcWQDHA.4024tk2msftngp13.phx.gbl...
    > Ohh, you are talking in that direction !!! actually you havent' put it in
    > proper words. The scenario what you are seeing is correct . since you are
    > joining on the table ACCOUNTINSURANCEPOLICIES table and if it has 2
    records
    > with the same accountid definitely it will fetch both the records. and you
    > will have different policy names on the basis of join for a single id.
    >
    > --
    > -Vishal
    > "Scott" <smorgansiuins.com> wrote in message
    > news:OZNjAZWQDHA.2676TK2MSFTNGP10.phx.gbl...
    > > Now that the light bulb went off, I realize my query was not right.
    What
    > I
    > > need is a distinct record from the accountinsurancepolicies table that
    has
    > > the same account Id that is in the account table. EX:
    > >
    > > ACCOUNT TABLE:
    > > ID - 3
    > > Amount: 100.00
    > >
    > > ACCOUNTINSURANCEPOLICIES TABLE:
    > > ACCOUNTID - 3
    > > POLICY: ABC123
    > >
    > > ACCOUNTID - 3
    > > POLICY: XYZ9876
    > >
    > > The Join is on ACCOUNTID = ID (from the account table)
    > >
    > > What I need is the record with the policy ABC123.
    > >
    > > Make sense?
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:urvixNWQDHA.3700tk2msftngp13.phx.gbl...
    > > > The resultset you've shown is giving you the distinct result set, are
    > > these
    > > > values repeating.?
    > > > Try:
    > > > Select distinct rtrim(ltrim(aip.policynumber)) as PolicyNumber
    > > > from accounts a
    > > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > > where a.bsuspended = 1
    > > >
    > > > --to find out whether there are really muliple values you can run
    query
    > > like
    > > > this.
    > > >
    > > >
    > > > Select count(*) cnt,aip.policynumber as PolicyNumber
    > > > from accounts a
    > > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > > where a.bsuspended = 1
    > > > group by aip.policynumber
    > > > having count(*) > 1
    > > >
    > > >
    > > > --
    > > > -Vishal
    > > > "Scott" <smorgansiuins.com> wrote in message
    > > > news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > > > I feel a little stupid sending this, but I must be having a mental
    > block
    > > > or
    > > > > something. I am trying to do a Select Distinct on a column from a
    > > table,
    > > > > but keep getting back multiple results. Below is my query and
    sample
    > > > > results.
    > > > >
    > > > > QUERY:
    > > > > Select distinct(aip.policynumber) as PolicyNumber
    > > > > from accounts a
    > > > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > > > where a.bsuspended = 1
    > > > > group by aip.policynumber
    > > > >
    > > > > RESULTS:
    > > > > PolicyNumber
    > > > > ABC1234
    > > > > XYX9876
    > > > >
    > > > > In the accountinsurancepolicies there are multiple rows with the
    same
    > > > > account number, I only want to return one. What am i missing here?
    > > > >
    > > > > Thanks in advance for your help.
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Scott Guest

  7. #7

    Default Re: Select Distinct

    there is but which one out of these will you pick up. here is one way using
    aggregate functions max/min etc.
    Ex:
    Select aip.accountid,max(aip.policynumber) as PolicyNumber
    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    where a.bsuspended = 1
    group by aip.accountid

    --
    -Vishal
    "Scott" <smorgansiuins.com> wrote in message
    news:u93hjoWQDHA.3192tk2msftngp13.phx.gbl...
    > Is there not a way to pull just one record from the
    ACCOUNTINSURANCEPOLICIES
    > Table?
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:OARHkcWQDHA.4024tk2msftngp13.phx.gbl...
    > > Ohh, you are talking in that direction !!! actually you havent' put it
    in
    > > proper words. The scenario what you are seeing is correct . since you
    are
    > > joining on the table ACCOUNTINSURANCEPOLICIES table and if it has 2
    > records
    > > with the same accountid definitely it will fetch both the records. and
    you
    > > will have different policy names on the basis of join for a single id.
    > >
    > > --
    > > -Vishal
    > > "Scott" <smorgansiuins.com> wrote in message
    > > news:OZNjAZWQDHA.2676TK2MSFTNGP10.phx.gbl...
    > > > Now that the light bulb went off, I realize my query was not right.
    > What
    > > I
    > > > need is a distinct record from the accountinsurancepolicies table that
    > has
    > > > the same account Id that is in the account table. EX:
    > > >
    > > > ACCOUNT TABLE:
    > > > ID - 3
    > > > Amount: 100.00
    > > >
    > > > ACCOUNTINSURANCEPOLICIES TABLE:
    > > > ACCOUNTID - 3
    > > > POLICY: ABC123
    > > >
    > > > ACCOUNTID - 3
    > > > POLICY: XYZ9876
    > > >
    > > > The Join is on ACCOUNTID = ID (from the account table)
    > > >
    > > > What I need is the record with the policy ABC123.
    > > >
    > > > Make sense?
    > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > news:urvixNWQDHA.3700tk2msftngp13.phx.gbl...
    > > > > The resultset you've shown is giving you the distinct result set,
    are
    > > > these
    > > > > values repeating.?
    > > > > Try:
    > > > > Select distinct rtrim(ltrim(aip.policynumber)) as PolicyNumber
    > > > > from accounts a
    > > > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > > > where a.bsuspended = 1
    > > > >
    > > > > --to find out whether there are really muliple values you can run
    > query
    > > > like
    > > > > this.
    > > > >
    > > > >
    > > > > Select count(*) cnt,aip.policynumber as PolicyNumber
    > > > > from accounts a
    > > > > join accountinsurancepolicies aip on aip.accountid = a.id
    > > > > where a.bsuspended = 1
    > > > > group by aip.policynumber
    > > > > having count(*) > 1
    > > > >
    > > > >
    > > > > --
    > > > > -Vishal
    > > > > "Scott" <smorgansiuins.com> wrote in message
    > > > > news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > > > > I feel a little stupid sending this, but I must be having a mental
    > > block
    > > > > or
    > > > > > something. I am trying to do a Select Distinct on a column from a
    > > > table,
    > > > > > but keep getting back multiple results. Below is my query and
    > sample
    > > > > > results.
    > > > > >
    > > > > > QUERY:
    > > > > > Select distinct(aip.policynumber) as PolicyNumber
    > > > > > from accounts a
    > > > > > join accountinsurancepolicies aip on aip.accountid =
    a.id
    > > > > > where a.bsuspended = 1
    > > > > > group by aip.policynumber
    > > > > >
    > > > > > RESULTS:
    > > > > > PolicyNumber
    > > > > > ABC1234
    > > > > > XYX9876
    > > > > >
    > > > > > In the accountinsurancepolicies there are multiple rows with the
    > same
    > > > > > account number, I only want to return one. What am i missing
    here?
    > > > > >
    > > > > > Thanks in advance for your help.
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  8. #8

    Default Re: Select Distinct

    That worked great - Thanks - I have one last question. I have one more
    column to pull from the accountinsurancepolicies table, except when I add it
    to the query, it goes back to pulling all the rows for the Account. Here is
    a Before and After example.
    BEFORE:
    Select
    aip.accountid,
    min(aip.policynumber) as PolicyNumber,
    i.name as InsuredName,
    i.address1 as InsAddress1,
    i.address2 as InsAddress2,
    i.city as InsCity,
    i.state as InsState,
    i.zip as InsZip,
    a.cancellationdate,
    a.suspenddate,
    c1.searchid as Agentno,

    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    left join insured i on i.id = a.insuredid
    left join company c1 on c1.id = ltrim(a.agentid)


    where a.bsuspended = 1
    group by aip.accountid,i.name,
    i.address1,
    i.address2,
    i.city,
    i.state,
    i.zip,
    c1.searchid,
    a.cancellationdate,
    a.suspenddate,

    EVERYTHING IS GREAT

    AFTER:

    Select
    aip.accountid,
    min(aip.policynumber) as PolicyNumber,
    i.name as InsuredName,
    i.address1 as InsAddress1,
    i.address2 as InsAddress2,
    i.city as InsCity,
    i.state as InsState,
    i.zip as InsZip,
    a.cancellationdate,
    a.suspenddate,
    c1.searchid as Agentno,
    c2.searchid as Company,

    from accounts a
    join accountinsurancepolicies aip on aip.accountid = a.id
    left join insured i on i.id = a.insuredid
    left join company c1 on c1.id = ltrim(a.agentid)
    left join company c2 on c2.id = ltrim(aip.ins_id)



    where a.bsuspended = 1
    group by aip.accountid,i.name,
    i.address1,
    i.address2,
    i.city,
    i.state,
    i.zip,
    a.cancellationdate,
    a.suspenddate,
    c1.searchid,
    c2.searchid


    THE NEW ITEM C2 IS WHERE I AM HAVING THE PROBLEM. WHEN I GO TO PULL JUST
    THE COMPANY #. I NEED IT TO RETURN JUST THE COMPANY # FOR THE POLICY IT
    RETURN.

    Any thoughts? - If I can get this, I will be out of your hair - LOL




    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:eXFkFsWQDHA.1556TK2MSFTNGP10.phx.gbl...
    > there is but which one out of these will you pick up. here is one way
    using
    > aggregate functions max/min etc.
    > Ex:
    > Select aip.accountid,max(aip.policynumber) as PolicyNumber
    > from accounts a
    > join accountinsurancepolicies aip on aip.accountid = a.id
    > where a.bsuspended = 1
    > group by aip.accountid
    >
    > --
    > -Vishal
    > "Scott" <smorgansiuins.com> wrote in message
    > news:u93hjoWQDHA.3192tk2msftngp13.phx.gbl...
    > > Is there not a way to pull just one record from the
    > ACCOUNTINSURANCEPOLICIES
    > > Table?
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:OARHkcWQDHA.4024tk2msftngp13.phx.gbl...
    > > > Ohh, you are talking in that direction !!! actually you havent' put it
    > in
    > > > proper words. The scenario what you are seeing is correct . since you
    > are
    > > > joining on the table ACCOUNTINSURANCEPOLICIES table and if it has 2
    > > records
    > > > with the same accountid definitely it will fetch both the records. and
    > you
    > > > will have different policy names on the basis of join for a single id.
    > > >
    > > > --
    > > > -Vishal
    > > > "Scott" <smorgansiuins.com> wrote in message
    > > > news:OZNjAZWQDHA.2676TK2MSFTNGP10.phx.gbl...
    > > > > Now that the light bulb went off, I realize my query was not right.
    > > What
    > > > I
    > > > > need is a distinct record from the accountinsurancepolicies table
    that
    > > has
    > > > > the same account Id that is in the account table. EX:
    > > > >
    > > > > ACCOUNT TABLE:
    > > > > ID - 3
    > > > > Amount: 100.00
    > > > >
    > > > > ACCOUNTINSURANCEPOLICIES TABLE:
    > > > > ACCOUNTID - 3
    > > > > POLICY: ABC123
    > > > >
    > > > > ACCOUNTID - 3
    > > > > POLICY: XYZ9876
    > > > >
    > > > > The Join is on ACCOUNTID = ID (from the account table)
    > > > >
    > > > > What I need is the record with the policy ABC123.
    > > > >
    > > > > Make sense?
    > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > news:urvixNWQDHA.3700tk2msftngp13.phx.gbl...
    > > > > > The resultset you've shown is giving you the distinct result set,
    > are
    > > > > these
    > > > > > values repeating.?
    > > > > > Try:
    > > > > > Select distinct rtrim(ltrim(aip.policynumber)) as PolicyNumber
    > > > > > from accounts a
    > > > > > join accountinsurancepolicies aip on aip.accountid =
    a.id
    > > > > > where a.bsuspended = 1
    > > > > >
    > > > > > --to find out whether there are really muliple values you can run
    > > query
    > > > > like
    > > > > > this.
    > > > > >
    > > > > >
    > > > > > Select count(*) cnt,aip.policynumber as PolicyNumber
    > > > > > from accounts a
    > > > > > join accountinsurancepolicies aip on aip.accountid =
    a.id
    > > > > > where a.bsuspended = 1
    > > > > > group by aip.policynumber
    > > > > > having count(*) > 1
    > > > > >
    > > > > >
    > > > > > --
    > > > > > -Vishal
    > > > > > "Scott" <smorgansiuins.com> wrote in message
    > > > > > news:OhUAHGWQDHA.2312TK2MSFTNGP12.phx.gbl...
    > > > > > > I feel a little stupid sending this, but I must be having a
    mental
    > > > block
    > > > > > or
    > > > > > > something. I am trying to do a Select Distinct on a column from
    a
    > > > > table,
    > > > > > > but keep getting back multiple results. Below is my query and
    > > sample
    > > > > > > results.
    > > > > > >
    > > > > > > QUERY:
    > > > > > > Select distinct(aip.policynumber) as PolicyNumber
    > > > > > > from accounts a
    > > > > > > join accountinsurancepolicies aip on aip.accountid =
    > a.id
    > > > > > > where a.bsuspended = 1
    > > > > > > group by aip.policynumber
    > > > > > >
    > > > > > > RESULTS:
    > > > > > > PolicyNumber
    > > > > > > ABC1234
    > > > > > > XYX9876
    > > > > > >
    > > > > > > In the accountinsurancepolicies there are multiple rows with the
    > > same
    > > > > > > account number, I only want to return one. What am i missing
    > here?
    > > > > > >
    > > > > > > Thanks in advance for your help.
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Scott Guest

Similar Threads

  1. Using SELECT DISTINCT
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 10:18 PM
  2. Select Distinct Statement Help
    By Ralph Freshour in forum PHP Development
    Replies: 5
    Last Post: August 20th, 07:27 AM
  3. SELECT DISTINCT from two tables
    By Marco Alting in forum ASP
    Replies: 4
    Last Post: July 31st, 10:26 AM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139