getting most recent record

[ashiers]

Hi there,

Given a table that has the following fields:

create table WAGE_RATES(
EMPLOYEE_ID int unsigned references EMPLOYEES,
DATE date,
HOURLY float(5,2),
WEEKLY float(5,2),
OTHER float(5,2),
index(DATE)
);

I want to run a SELECT query that uses the EMPLOYEE_ID number to compare all available records and return only the most recently entered record making use of the DATE field. What does the query look like? How do you make the database return a record based on the most recent date?

Alan

 

[towerbase]

Something like

SELECT * FROM WAGE_RATES ORDER BY DATE DESC LIMIT 1

should give you an idea of how to implement your query

Andrew
Hampshire, UK

 

[r937]

if you want the latest row per employee,

select EMPLOYEE_ID
     , `DATE`
     , HOURLY 
     , WEEKLY 
     , OTHER
  from WAGE_RATES as W
 where `DATE`
     = ( select max(`DATE`)
           from WAGE_RATES 
          where EMPLOYEE_ID 
              = W.EMPLOYEE_ID )

r937.com | rudy.ca