Showing posts with label distinct. Show all posts
Showing posts with label distinct. Show all posts

Friday, March 23, 2012

Need distinct rows from table based on (col1) only - overall required multiple c

Need rows of specific table having distinct col1, but along with col1 I also need col2,col3, .... But distinct is required on only col1.

I tried ---
Select col1, col2, ... , coln from table1 where ( select distinct col1 from table1)

But result contain repetitive col1 rows due to wrong syntax of mine. Also tried exist, union, right join but failed finally ....

give your some site:
one is mser.net,another is msdnx.com,search by google in the site....

Friday, March 9, 2012

Need a way to exclude duplicate information

I've created a query:
SELECT DISTINCT co.CL_Desc,a.AN_Key,a.CL_Code,a.AT_Code,a.AN_Refer ence,a.AN_ToDo,a.An_Closed
,at2.CU_Value2,an1.CU_Value1,at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN Action a ON co.CL_Code = a.CL_Code /* Get actions */
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

And it returns somthing like

CompanyA,Enquiry,04/09/2003
CompanyA,Contract,05/09/2003
ComanyB,Enquiry,04/09/2003

And so on
I know this is not true duplcates but for me its a problem. I only want the last record of each company to display being the most recent record. I thought I union query may have helped to solve this, but no luck has anyone got any ideas?What is your last record? The record with the highest ID per company? Select those ID's per company, and INNER JOIN the result with your original table!|||DB's general solution in code:
(I assumed that AN_Closed was the date value and that you wanted the latest one, rather than an ID value.) Because you are using OUTER joins, I think you need two levels of nesting to be sure you get all the data you want, but I couldn't test the code.

SELECT DISTINCT co.CL_Desc, a.AN_Key, a.CL_Code, a.AT_Code, a.AN_Reference, a.AN_ToDo, a.An_Closed, at2.CU_Value2, an1.CU_Value1, at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN /* Get last actions */
LEFT OUTER JOIN
(select An_Key, CL_Code, AT_Code, AN_Reference, AN_ToDo, AN_Closed
from Action
inner join (select CL_Code, max(AN_Closed) Last_AN_Closed from Action group by CL_Code) Last_Values
on Action.CL_Code = Last_Values.CL_Code and Action.AN_Closed = Last_Values.Last_An_Closed) a
ON co.CL_Code = a.CL_Code
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

blindman