Complicated

[johnsmith153]

 

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.

[sunfighter]

1.)Your definition of grouped;

if field1 and field2 are blank

 

and what you said were grouped;

So record 1 and 3 above will be grouped

 

is not the same. (both fields are not empty)

 

2.)Do you know all the unique employeeID's so you can run a query from a list?

 

3.)If 32 / 3 /  /  1297184426 is in a group and 20 /  /  /  1268722384 is in a group,

what is 20 / 3 / b /  1268722384 a different group or part of the second example?

 

4.) can you modify this database?

 

5.)can you create a temporary database for your own use?

[johnsmith153]

http://www.sitepoint.com/forums/showthread.php?t=732255

 

Actually it's not complicated when you know how. Very simple.