Last post Nov 05, 2020 02:56 AM by yij sun
Nov 04, 2020 01:50 PM|Baiju EP|LINK
In my asp.net+VB=SQL web i have a query for dropdownlist
Dim comd2 As New SqlCommand("SELECT distinct EmpNo from Leave ORDER BY CAST(SUBSTRING(EMPNO + '0', PATINDEX('%[0-9]%', EMPNO + '0'), LEN(EMPNO + '0')) AS INT)", con2)
If I remove DISTINCT from the above query it works for me.
But want to pick DISTINCT EMPNO from table leave
Error is as under
Nov 05, 2020 02:56 AM|yij sun|LINK
Hi Baiju EP,
When you are using the Select Distinct with Order By, the Columns specified in the Order By Clause must be available in the Select Statement As Well.
You could rewrite the query using group by.
Just like this:
select EmpNo from Leave GROUP BY EmpNo ORDER BY CAST(SUBSTRING(EMPNO + '0', PATINDEX('%[0-9]%', EMPNO + '0'), LEN(EMPNO + '0')) AS INT)