Complicated query - help needed

Databases
1

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.

2

Will you tell us what type of database it is?

3

MySQL

4

in order for this query to have any chance, you must tell us what “empty” means

equal to a zero-length string? or actually NULL? or either?

5

zero-length string

6

that makes it a bit simpler :slight_smile:

SELECT employeeID 
     , field1 
     , field2
     , CASE WHEN MIN(date) = ''
            THEN ''
            ELSE MAX(date) END  AS date
  FROM daTable
GROUP
    BY employeeID 
     , field1 
     , field2