CODEWITHSUNDEEP
mysql
Sreenath Reddy
Sreenath Reddy

Reputation: 490

MYSQL get all the results of a date less than given value

I have a table as below

Eid  joinDate

1    01-01-2016
2    01-01-2016
3    28-02-2016
4    28-02-2016
5    28-02-2016
6    15-02-2016
7    15-02-2016

I want to get all the results of a most recent date which has data.

ex: if I give '28-02-2016' as input, then the results should be

3    28-02-2016
4    28-02-2016
5    28-02-2016

If I give '18-02-2016' as input the results should be

1    15-02-2016
2    15-02-2016(Most recent from 18-02-2016)

I tried with the below query, but it is returning only one result.

select Eid,max(joinDate) from Employee where joinDate<='18-02-2016'

But this query is not returning all the results. Also if I use group by then it will return all results less than 18-02-2016

Is there any other way to achieve this, please suggest.

Upvotes: 2

Views: 1931

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can do it using LEFT JOIN:

SELECT t1.Eid,
   t1.joinDate
FROM Employee AS t1
LEFT JOIN Employee AS t2 ON t2.joinDate > t1.joinDate
AND t2.joinDate <= '2016-02-16'
WHERE t1.joinDate <= '2016-02-16'
  AND t2.Eid IS NULL

SQL Demo here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this in MySQL by using a subquery in the WHERE clause:

select e.*
from employees e
where e.joindate = (select max(e2.joindate)
                    from employees e2
                    where e2.joindate <= '2016-02-28'
                   );

Upvotes: 0

Related Questions