Using Distinct and Order By Clause together?

[[email protected]]

Is it possible to use Distinct and Order by Clause in the same query together.. if so how to write it ?


Wasim Zafar

[Hack]

Sure it is possible. Are you having a problem with such a query?

What are you trying to do?

[[email protected]]

I have write. but it shows the error ORDER BY CLAUSE (SubcategoryTbl.SubCategoryNo) conflicts with DISTINCT..


Here is the Query.

SELECT DISTINCT PatientTbl.PatientName, PatientTbl.Address, PatientTbl.Age, PatientTbl.Sex, TestMasterTbl.LabNo, TestMasterTbl.TestDate, TestMasterTbl.PatientType, CategoryTbl.Category, SubCategoryTbl.SubCategory, TestGroupMasterTbl.Group, TestListTbl.Test, TestListTbl.NormalRange, TestDetailTbl.Value
FROM (PatientTbl INNER JOIN TestMasterTbl ON PatientTbl.PatientId = TestMasterTbl.PatientId) INNER JOIN (TestListTbl INNER JOIN ((((CategoryTbl INNER JOIN TestGroupMasterTbl ON CategoryTbl.CategoryNo = TestGroupMasterTbl.CategoryNo) INNER JOIN TestDetailGroupTbl ON TestGroupMasterTbl.GroupNo = TestDetailGroupTbl.GroupNo) INNER JOIN TestDetailTbl ON TestGroupMasterTbl.GroupNo = TestDetailTbl.GroupNo) INNER JOIN (SubCategoryTbl INNER JOIN TestGroupDetailTbl ON SubCategoryTbl.SubCategoryNo = TestGroupDetailTbl.SubCategoryNo) ON TestGroupMasterTbl.GroupNo = TestGroupDetailTbl.GroupNo) ON (TestListTbl.TestNo = TestGroupDetailTbl.TestNo) AND (TestListTbl.TestNo = TestDetailTbl.TestNo)) ON TestMasterTbl.LabNo = TestDetailTbl.LabNo
WHERE (((TestMasterTbl.LabNo)=[?]))
ORDER BY SubCategoryTbl.SubCategoryNo;


Please help.
Wasim Zafar

[brucevde]

From SQL Server help.

The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Add SubCategoryNo to the select list or sort by a different field.

[[email protected]]

Thanks ...

It is done now. You got the problem.

Thanks


But i am facing a problem the same query display in sort order in access but when i execute it in vb6 the sort doesn't work.

Can u guide ...?