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.
SELECT DISTINCT [IpMain].[Email-ID]
LEFT JOIN CRVMGridTable
WHERE IpWhere = 'IPTRI'
AND (StatusIpTwo Like 'CMR')
AND ([Mkt-M] Like 'NOEXISTE')
ORDER BY IpMain.Date DESC;
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.
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.