Results 1 to 11 of 11

Thread: Distinct record

  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Distinct record

    Hello Experts,

    I have a following query which is working fine.

    SELECT [IpMain].*, [IpMain].ConsultantName,[IpMain].[Email-ID],[IpMain].[Mkt-M],[IpMain].[Rec-M],[IpMain].[Tier-1],[IpMain].ENDClient, [IpMain].Date,'(' + [IpMain].Factor + ')' AS Factor,[IpMain].intDate,[IpMain].VenFrom,[IpMain].FComments,[IpMain].ReqIDCRVM ,[CRVMGridTable].Cell,[CRVMGridTable].VEmail,[CRVMGridTable].VCell From IpMain LEFT JOIN CRVMGridTable ON Ipmain.ReqIDCRVM=CRVMGridTable.ReqId WHERE IpWhere='IPTRI' AND (StatusIpTwo Like 'CMR') and ([Mkt-M] Like 'NOEXISTE') order by IpMain.Date desc;

    I want this query picks only distinct records based on [IpMain].[Email-ID].
    how to do that.
    Please advise me.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what table is IpWhere in?

    what table is StatusIpTwo in?

    what happens if a single IpMain row has multiple CRVMGridTable rows? will they all have the same VEmail value as IpMain.[Email-ID]? which one of the CRVMGridTable rows did you want?

    what happens if multiple rows of IpMain have the same [Email-ID]? how do you decide which one you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT DISTINCT [IpMain].[Email-ID]
       FROM IpMain
       LEFT JOIN CRVMGridTable
          ON Ipmain.ReqIDCRVM=CRVMGridTable.ReqId
       WHERE IpWhere = 'IPTRI' 
          AND (StatusIpTwo Like 'CMR') 
          AND ([Mkt-M] Like 'NOEXISTE')
       ORDER BY IpMain.Date DESC;
    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, your ORDER BY clause is wrong, after the DISTINCT is applied there is no date column to sort on

    also, he specifically asked for distinct records, not distinct emails

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    16
    IpWhere and StatusIpTwo they both are in IpMain.

    what happens if a single IpMain row has multiple CRVMGridTable rows? will they all have the same VEmail value as IpMain.[Email-ID]? which one of the CRVMGridTable rows did you want?

    what happens if multiple rows of IpMain have the same [Email-ID]? how do you decide which one you want?



    I am facing the same problem as me query fetches multiple rows of IpMain have the same [Email-ID].That's why i thought may be the solution is DISTINCT.
    That's why i am asking how to use that?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i don't have enough information to offer a solution

    pat?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    pat, your ORDER BY clause is wrong, after the DISTINCT is applied there is no date column to sort on
    Please describe "wrong" in a bit more detail. This use of ORDER BY is definitely is not compliant with the ISO definition, but it works just fine in Microsoft SQL Server. Follow along with my reasoning, and I think you'll understand why I included it.
    Quote Originally Posted by r937
    also, he specifically asked for distinct records, not distinct emails
    Records exist in programming languages, not in SQL so any discussion of records at the server level is moot. Since records correspond to rows in a result set and this request is asking for distinct values of [Email-ID], I think that my query will return what was requested or at least as close as possbile without getting a better problem specification.\

    Because the original poster (OP) specified a sort order, I assume that it makes sense to return the [Email-ID] values in as close to the order generated by the original query as possible. Since Microsoft SQL allows columns to be used to order a result set even if those columns aren't included in the result set, that was the simplest way to achieve my interpretation of the original request.

    I think that I provided the best answer that I can given the problem specification.

    -PatP

  8. #8
    Join Date
    Nov 2008
    Posts
    16
    is anybody else have any idea abt this?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by database.child
    is anybody else have any idea abt this?
    Try my first post. Does that help?

    -PatP

  10. #10
    Join Date
    Nov 2008
    Posts
    16
    PatP,
    I tried your query but it does not work.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by database.child
    PatP,
    I tried your query but it does not work.
    oh yes it does!!!

    however, the LEFT JOIN is very superfluous in pat's query

    i suspect that he threw it in just to keep you on your toes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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