Is it possible to use Distinct and Order by Clause in the same query together.. if so how to write it ?
Wasim Zafar
Printable View
Is it possible to use Distinct and Order by Clause in the same query together.. if so how to write it ?
Wasim Zafar
Sure it is possible. Are you having a problem with such a query?
What are you trying to do?
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
From SQL Server help.
Add SubCategoryNo to the select list or sort by a different field.Quote:
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.
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 ...?