I have a data like this that came from two tables.
FirstName LastName LeaveType LeaveDateFrom LeaveDateTo
Jane Doe VL 2019/01/02 2019/01/02
Joe Armstrong BVL 2019/01/02 2019/01/02
Joe Armstrong BVL 2019/01/01 2019/01/01
Joe Armstrong BVL 2018/12/31 2018/12/31
Joan Day VL 2018/12/31 2018/12/31
Ivan Reyes VL 2018/12/28 2018/12/28
Joe Armstrong BVL 2018/12/27 2018/12/27
John Doe VL 2018/12/27 2018/12/27
Jake Doe VL 2018/12/27 2018/12/27
Joe Armstrong BVL 2018/12/26 2018/12/26
The data above is a result of the following query:
SELECT emp.Emp_Fname as FirstName,
emp.Emp_Lname as LastName, lt.LeaveType as LeaveType,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateFrom,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateTo
FROM py_leavetable lt
LEFT JOIN py_emp_master emp ON lt.EmpCd = emp.Emp_Cd
WHERE emp.date_resign IS NULL
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) >= 20181101
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) <= 20190130
ORDER BY lt.TimeIn DESC
I tried adding the name fields in the ORDER BY clause but it didn't change the results. So I tried using GROUP BY. However, I ended up needing to add all the fields because it keeps returning aggregate function error. I tried researching further and it seems I need to do "mathematical operations" for the GROUP BY to work but I don't think it is applicable for the data I have. But, I could be wrong since I'm not really very good at SQL.
Can anyone help me or point me in the right direction on how I can achieve the following results:
FirstName LastName LeaveType LeaveDateFrom LeaveDateTo
Jane Doe VL 2019/01/02 2019/01/02
Joe Armstrong BVL 2019/01/02 2019/01/02
Joe Armstrong BVL 2019/01/01 2019/01/01
Joe Armstrong BVL 2018/12/31 2018/12/31
Joe Armstrong BVL 2018/12/27 2018/12/27
Joe Armstrong BVL 2018/12/26 2018/12/26
Joan Day VL 2018/12/31 2018/12/31
Ivan Reyes VL 2018/12/28 2018/12/28
John Doe VL 2018/12/27 2018/12/27
Jake Doe VL 2018/12/27 2018/12/27
It would even be better if I'm able to do it like this but having the result above should do:
FirstName LastName LeaveType LeaveDateFrom LeaveDateTo
Jane Doe VL 2019/01/02 2019/01/02
Joe Armstrong BVL 2018/12/26 2019/01/02
Joan Day VL 2018/12/31 2018/12/31
Ivan Reyes VL 2018/12/28 2018/12/28
John Doe VL 2018/12/27 2018/12/27
Jake Doe VL 2018/12/27 2018/12/27
Am I making sense here? Can anyone help me figure this out? Thanks!
Best Answer
When adding fields to an ORDER BY clause, you must put them in the correct sequence. If your order by clause went from
to
then the result set will be sorted first by the TimeIn value, then by name for those rows that had matching TimeIn values.
Assuming that you want to sort by Last Name, then First Name, then Time In, you would use the order by:
This will sort first by the Last Name, then First Name, then within those sorted groups you would sort by Time In, giving you the results you are hoping for.