max date less than current

[sbarlow]

I currently have a table that contain a list of salary numbers and dates. Each salary number has more than one date. I need to get a table containg salary number, *date, and max date less than *date. So far have been unable to achieve this, Please Help.

 

[Tim1]

Hiya,

The command is very simple:

SELECT a.field1, a.field2
FROM table a
WHERE *DATE = (SELECT MAX(*DATE)
FROM table b
WHERE b.primary_key = a.primary_key)

The substitute the field names that you want into the first select statement, and into the where clause of the subquery, change primary_key to whatever field is your unique reference (e.g. employee id etc.)

HTH

Tim

 

[sparrow]

How about

QueryOne
Select Salary, Date from Table Where Date < *Date

QueryTwo
Select Salary, max(Date) from QueryOne




Paul