CODEWITHSUNDEEP
phpmysql
Nisanth
Nisanth

Reputation: 333

select records with max date

I have a table like below :

Id  fkEmp_id  Date          Type
1     1       2016-08-20      1
2     2       2016-08-28      1
3     2       2016-08-30      0

I want to select records based on "Type" <> 0 and the " Date " should be Maximum Date for each employees.

I try the query below :

select *  from table where Type <> 0 and fkEmp_Id = 2 order by max(Date)

But the result is look like below :

 Id  fkEmp_id   Date           Type 
  2    2         2016-08-28     1

If the Type is 1 and the Date is Max for each FkEmp_id then only I want to fetch the records . How to do that? Please help me.

Upvotes: 2

Views: 1776

Answers (3)

Raj
Raj

Reputation: 45

You can use subquery as follows:

SELECT max(date), fkemp_id from emp
where fkemp_id not in (Select fkemp_id from emp where type = 0)
GROUP by fkemp_id

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

So, start with a query that gets the "maximum date" for each employee fkemp_id.

Then use that query as an inline view. Join that to the original table to filter out all rows except the rows where type<>0 and date matches the "maximum date" for the given fkemp_id.

For example:

SELECT t.id
     , t.fkemp_id
     , t.date
     , t.type
  FROM ( -- inline view query to return maximum date for each fkemp_id
         SELECT n.fkemp_id
              , MAX(n.date) AS max_date
           FROM mytable n
          GROUP BY n.fkemp_id
       ) m
  JOIN mytable t
    ON t.fkemp_id = m.fkemp_id
   AND t.date     = m.max_date
   AND t.type    <> 0
 ORDER BY t.fkemp_id

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

This is a simple typical aggregate (GROUP BY) query.

 SELECT MAX(Date) as MaxDate, fkEmp_id  
   FROM table
  WHERE Type <> 0
  GROUP BY fkEmp_id

Upvotes: 1

Related Questions