I have inherited this database and can’t change the design.
I need to perform a SELECT query and group certain records together for matching employeeID values.
The fields:
employeeID
field1
field2
date
(1) field1 can be 1,2,3,4 or 5 only
(2) field2 can be a,b,c or a number from 1-100 (varchar though)
(3) if field1 and field2 are blank/empty then group together
So:
employeeID / field1 / field2 / date
32 / 3 / / 1297184426
45 / / b / 1248723499
32 / 3 / / 1258762988
20 / / /
20 / / / 1268722384
So record 1 and 3 above will be grouped, and so will 4 and 5.
When I mean grouped, I mean so that only one record will be returned / displayed.
Also, when it displays only one record due to being ‘grouped’, I would like it to return the highest ‘date’ field value (UNIX Timestamp value) (so the newest / latst date) OR if one of the records is empty then show this instead. So in the above example, record 1 & 3 will return a date of 1297184426 and records 4 and 5 will return nothing for date.