My aim is to select a rows that have a distinct LeaveApplied where duplicates are present on remaining columns, see the example table "Leave" below:
| LeaveApplied | title | department | bank | joiningDate | exitDate |
|---|---|---|---|---|---|
| 14-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
| 17-Mar-24 | Associate | production | hsbc | 13-Mar-24 | 14-Mar-24 |
| 17-Mar-24 | Associate | quality | kvb | 14-Mar-24 | 13-Mar-24 |
| 18-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
| 19-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
| 20-Mar-24 | Associate | quality | kvb | 14-Mar-24 | 13-Mar-24 |
I tried below query but did not help me.
SELECT LeaveApplied, title, department, bank, joiningDate, exitDate
COUNT(*) occurrences FROM leave
GROUP BY title, department, bank, joiningDate, exitDate HAVING COUNT(*) > 1
Ended with error: Column 'Leave.LeaveApplied' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The desired result would be:
| LeaveApplied | title | department | bank | joiningDate | exitDate |
|---|---|---|---|---|---|
| 14-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
| 18-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
| 19-Mar-24 | Associate | quality | hsbc | 14-Mar-24 | 14-Mar-24 |
Can I have query or guidance to get desired result?